Creating a Macro

By recording a series of macro instructions into a macro module or macro sheet in a workbook, you can tell Excel to perform any series of commands or actions for you. A macro can take the place of any mouse or keyboard action that you can perform in Excel. That is, a macro can cause Excel to accomplish a task by itself. You simply record a macro that shows Excel what you want to accomplish. Then Excel can repeat the task at any time.

You can create a macro by manually typing the instructions in a macro sheet or by choosing Tools, Macro, Record. Manually creating a macro requires you to carefully write down each step of the macro in the macro sheet. A single misspelling can affect the operation of the macro. Choosing Tools, Macro, Record, on the other hand, simply records each movement and action you take while using Excel. When you have completed the action, you stop recording by choosing the Stop Recording button on the Stop Recording toolbar. If you make a mistake while recording your macro, you can edit the macro later.

For most purposes, then, you should use Tools, Macro, Record to create macros. This method ensures that your macro will work when you use it.

Naming the Macro

A macro name can have up to 256 characters with no spaces. It's best to make your macro names meaningful and short so that you and others can quickly discern which macro to use. You name the macro right after you select Tools, Macro, Record New Macro.

The default macro name that Excel assigns to a macro is the word Macro followed by a number that looks like this: Macro1, Macro2, and so on. The name appears highlighted in the Macro Name box when the Record Macro dialog box first appears. That way, you can easily type right over the default name with any name you want. Remember, a name cannot contain spaces. If you enter a space anywhere in the name, Excel will not accept the macro name.

Selecting a Keyboard Shortcut

All macro shortcut keys must include the Ctrl key in combination with one other keyboard key. You can also use the Shift key in combination with the Ctrl key when assigning the shortcut key. For instance, you might assign the key combination Ctrl+Shift+F to run the Font_change macro.

Excel reserves many Ctrl shortcut key assignments for its own use. Excel will tell you when a combination key is already assigned and won't let you use an existing shortcut key. To avoid conflicts with these existing key assignments, you should use the Ctrl+Shift key combination for your shortcut keys.

Describing the Macro

An optional step is to enter a description of your macro to explain its function. A description can be helpful for you and others who use the macro. The default description contains the date you created or last modified the macro and your user name. In the example of the Font_change macro, you might want to explain that the macro changes the font and font size for data.

Recording the Macro

A macro is recorded on using the currently selected tab of the current worksheet. When you record a macro, Excel displays the Stop Recording toolbar that contains two buttons : Stop Recording and Relative Reference. The Stop Recording button does just what it saysit stops the recording of a macro. The Relative Reference button allows you to switch between relative and absolute references. By default, Excel records absolute cell references unless you click the Relative Reference button on the Stop Recording toolbar to specify that a cell or range of cells should be a relative reference. When you choose relative reference, the Relative Reference button appears depressed on the toolbar. Click the Relative Reference button again to switch back to absolute reference. The button no longer appears depressed.

This To Do exercise guides you through creating a macro called Font_change that changes the font and font size for data on your worksheet. You should be using the Sales 1st Qtr workbook you've worked with before.

To Do: Create and Record a Macro

Open the Sales 1st Qtr workbook. This workbook will contain the macro. Click cell B80 so that when you perform actions to record the macro, it will not change anything you didn't intend to change.
Choose Tools, Macro and click Record New Macro. The Record Macro dialog box pops up, as shown in Figure 20.2. You should see the default macro name Macro1 in the Macro Name box. You want to change that name to Font_change .

Figure 20.2. The Record Macro dialog box.
In the Macro Name box, type Font_change . This step names the macro.

Press Tab. In the Shortcut Key box, hold down Shift and type F . This step assigns the shortcut key Ctrl+Shift+F to the macro.

When you run the macro, press the appropriate letter key along with the Ctrl key, without pressing the Shift key, unless the Shift key is part of the shortcut key sequence. For example, Ctrl+Shift+F.

Click OK. The Record Macro dialog box disappears. Notice the recording indicator in black letters at the left end of the status bar at the bottom of the Excel window. From this point on, until you choose Stop Recorder, Excel stores every action and command on a macro sheet. Also note the Stop Recording toolbar that contains two buttons: Stop Recording and Relative Reference. Figure 20.3 shows you what your worksheet looks like just before you begin to record a macro.

Figure 20.3. Recording a macro.
Click the Font drop-down arrow on the Formatting toolbar and choose Arial.
Click the Font Size drop-down arrow on the Formatting toolbar and choose 22. Now you're finished recording the macro, so stop the recorder.
Click the Stop Recording button on the Stop Recording toolbar. Excel hides the toolbar and stops recording the macro. You can tell because the Recording indicator in the status bar disappears. You have now recorded a macro that changes the font and font size for data on the worksheet.
Save the workbook. This step saves your macro, too.

Saving the Macro

When you save your workbook, Excel saves your macro on the macro sheet with the workbook. You don't have to do anything else to save the macro. If you accidentally close the workbook without saving changes, Excel doesn't save the macro. You have to start all over and re-create the macro.