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 a macro sheet in a workbook. 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.
The following steps guide 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 My Budget workbook you've worked with before.
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.