1.6 Putting It Together: Recording Your First Macro

As a specific example, let's assume that you frequently format cells you want to draw attention to in a 12-point bold red Arial font, but without changing any other part of the cells' formatting. You can't use an Excel named style (accessed through the Cell Styles button on the Home ribbon tab) to apply the desired character formatting because you'd affect all the cells' attributes (such as date or currency formatting), not just the character format. Instead, you must set the font attributes manually each time.

Selecting the Arial font, adjusting the font size, applying the bold attribute, and applying the font color to the cell involve several mouse or keyboard operations. To reduce the amount of time required to format the text, you decide to record a macro that applies the desired formatting to the currently active cell or selected range.

The remainder of this section takes you step-by-step through the process of recording a new macro to apply the desired font characteristics.

Set Up the Starting Conditions

Because you want this macro to work on any selected cell or range of cells, the starting conditions for this macro are an open workbook with one or more cells selected on the active worksheet. To set up the starting conditions for this specific example in Excel, follow these steps:

Start Excel, it if isn't already running.
Open any workbook.
Select any worksheet.
Select any cell on the worksheet. Figure 1.2 shows a sample workbook open with a single cell selected.


Figure 1.2 – Starting conditions for the text-formatting macro.

By selecting a cell in an open worksheet, you have created the necessary starting conditions to record this general-purpose macro for applying character formatting to any selected cell or range of cells in an Excel worksheet.

Name the Macro and Select a Storage Location

Now you are ready to start the Excel Macro Recorder, name the macro, choose a location to store the new macro, and select additional options. Follow these steps:

Choose Record Macro on the Developer tab. Excel displays the Record Macro dialog shown in Figure 1.3.


Figure 1.3 – The Excel Record Macro dialog.

In the Macro Name text box, type FormatArial Red Bold12 as the name of the macro. This name helps you remember what this macro does.
In the Shortcut key text box, type F. (That is, type the uppercase letter "F.") The keystroke display will change to show that the shortcut key is Ctrl+Shift, plus your entry in the text box.
Use the Store Macro In drop-down list to select a location in which to store the recorded macro. Because you want this macro to be available in any work session, choose Personal Macro Workbook.
Type the following text into the Description text box: Format selected cell s text with Arial bold , red, 12 - point font. This additional descriptive comment helps you (and others) determine what the macro's purpose is. Figure 1.4 shows the Record Macro dialog with its options filled in.
Click the OK button to start recording your macro.

When you select the Personal Macro Workbook as the location in which to store your new macro, Excel stores the macro in a special workbook file named Personal.xlsb in the Excel startup folder. Excel automatically opens workbook files stored in its startup folder each time you start Excel. Because macros in any open workbook are available for use, storing a macro in the Personal.xlsb workbook causes it to be available in any Excel work session. When you select Personal Macro Workbook as the storage location for your new macro, Excel creates the Personal.xlsb workbook file if it doesn't already exist. If you installed Excel using the default folders, Excel's startup folder is typically \Users\username\AppData\Roaming\Microsoft\Excel\XLSTART. (The AppData folder is a hidden folder; you will only be able to see it in the file browser if you set the File Explorer options to show hidden files and folders.)

You might not notice that the Personal.xlsb workbook is open because, by default, Excel hides the Personal.xlsb workbook file after opening it. (Refer to Excel's online help for information on hiding and unhiding workbook files.)

Figure 1.4 shows the Record Macro dialog filled in for the FormatArial Red Bold12 example macro.


Figure 1.4 – The completed Record Macro dialog for the FormatArialRedBold12 macro.

Follow these guidelines when choosing the options for your recorded macros:

Store general-purpose Excel macros in the Personal.xlsb workbook.
Store macros that relate specifically to a single workbook in the workbook to which the macro relates.
Remember that Excel macro shortcut keys are the combination of the keystroke you enter plus the Ctrl key – if you type a in the Shortcut Key text box, the actual shortcut keystroke is Ctrl+a. If you type A as the shortcut key, the resulting shortcut keystroke is Ctrl+Shift+A.

Only assign a shortcut key to a macro when you really do expect to use the macro with great frequency – if you assign shortcut keys to all of your Excel macros, you'll quickly run out of unassigned keys.

Record Your Actions

As soon as you click the OK button in the Record Macro dialog, Excel starts the Macro Recorder, and begins recording your actions. Excel's Macro Recorder stores each action you perform in the new macro until you stop the Macro Recorder.

During recording, the Record Macro button on the Developer tab is replaced by the Stop Recording button. Also, in the Excel status bar at the bottom left side of the Excel window, a square button appears; clicking this button will also stop macro recording.

By default, Excel records absolute cell references in your macros. If you start recording with cell B4 selected, for example, and then select the cell to the right of B4 – that is, cell C4 – your recorded macro also selects cell C4 each and every time it runs.

Excel gives you the option of recording with relative references. When you record with relative references, Excel records cell references as being relative to the last selected cell or range. For example, if the currently selected cell is B4, and you select the cell to its right while recording with relative references, Excel records that you selected a cell 1 column and 0 rows to the right of the current cell. When you run the recorded macro, it selects whatever cell is immediately to the right of the active cell.

You choose whether or not to use relative references by clicking the Use Relative References button on the Developer tab. The Use Relative References button is a toggle button – that is, it turns relative reference recording on and off. When relative reference recording is off, the Use Relative References button has a flat appearance, without any color (except when you move the mouse pointer over it). When relative reference recording is on, the Use Relative References button remains highlighted whether or not the mouse is over it.

You can turn relative reference recording on and off as you want during recording.

To complete the FormatArialRedBold12 macro, perform these actions:

Right-click on the current selected cell or range, and choose Format Cells from the popup menu. Excel displays the Format Cells dialog.
Click the Font tab to display the Font options, if necessary. (Figure 1.5 shows the Format Cells dialog with the Font options displayed.)


Figure 1.5 – The Format Cells dialog after setting font options for the FormatArialRedBold12 macro.

Select Arial in the Font list. (Perform this step even if the Arial font is already selected.)
Select Bold in the Font Style list.
Select 12 in the Size list.
Select a dark red color in the Color drop-down list.
Choose OK to close the Format Cells dialog and apply the changes to the selected cell in the worksheet.

Stop the recorder as described in the next section.

Stop the Macro Recorder

When you have completed the series of actions that you want to record, you should immediately stop Excel's Macro Recorder.

To stop the Macro Recorder, do one of these things:

Click the Stop Recording button on the Developer ribbon.
Click the square at the bottom left region of the Excel status bar.

For the FormatArialRedBold12 example macro, you should stop the Macro Recorder immediately after you choose OK to close the Format Cells dialog (that is, immediately after completing step 7 in the previous section). Your new Excel macro is now complete and ready to run. You'll learn how to run your new macro in the next section.

Previous Next