start recording your first macro, you first need to find the macro recorder, which is on the Developer tab. Unfortunately, Excel comes out of the box with the Developer tab hidden—you may not see it on your version of Excel at first. To display this tab, follow these steps:
1 Choose File ➪ Excel Options.
2 In the Excel Options dialog box, select Customize Ribbon.
3 In the list box on the right, place a check mark next to Developer.
4 Click OK to return to Excel.
Now that you have the Developer tab showing in the Excel Ribbon, you can start up the macro recorder by selecting the Record Macro command found in the Code group on the Developer tab. This activates the Record Macro dialog box, as shown in Figure 2.1.
FIGURE 2.1 The Record Macro dialog box
NOTE
Note that you can also get to the Macro Recorder by selecting View ➪ Macros ➪ Macros ➪ Record Macros. However, if you plan to work with VBA macros, you'll want to make sure that the Developer tab is visible in order to gain access to the full gamut of developer features.
Here are the four parts of the Record Macro dialog box:
Macro Name This should be self-explanatory. Excel gives a default name to your macro, such as Macro1, but you should give your macro a name more descriptive of what it actually does. For example, you might name a macro that formats a generic table as FormatTable.
Shortcut Key Every macro needs an event, or something to happen, for it to run. This event can be a button press, a workbook opening, or, in this case, a keystroke combination. When you assign a shortcut key to your macro, entering that combination of keys triggers your macro to run. This is an optional field.
Store Macro In This Workbook is the default option. Storing your macro in This Workbook simply means that the macro is stored along with the active Excel file. The next time you open that particular workbook, the macro is available to run. Similarly, if you send the workbook to another user, that user can run the macro as well (provided the macro security is properly set by your user—more on that later in this chapter).
Description This is an optional field, but it can come in handy if you have numerous macros in a workbook or if you need to give a user a more detailed description about what the macro does. The description is also useful for distinguishing one macro from another when you have multiple workbooks open or you have macros stored in the Personal Macro Workbook.
With the Record Macro dialog box open, follow these steps to create a simple macro that enters your name into a worksheet cell:
1 Enter a new single-word name for the macro to replace the default Macro1 name. A good name for this example is MyName.
2 Assign the shortcut key Ctrl+Shift+N to this macro by entering uppercase N in the edit box labeled Shortcut Key.
3 Click OK to close the Record Macro dialog box and begin recording your actions.
4 Type your name into the active cell and press Enter.
5 Choose Developer ➪ Code ➪ Stop Recording. Alternatively, you can click the Stop Recording icon in the status bar (the square icon on left side of the status bar).
Examining your macro
Excel stored your newly recorded macro in a new module that it created automatically and named Module1. To view the code in this module, you must activate the Visual Basic Editor. You can activate the VB Editor in either of two ways:
Press Alt+F11.
Choose Developer ➪ Code ➪ Visual Basic.
In the VB Editor, the Project window displays a list of all open workbooks and add-ins. This list is displayed as a tree diagram on the left of the screen, which you can expand or collapse. The code that you recorded previously is stored in Module1 in the current workbook. When you double-click Module1, the code in the module appears in the Code window.
NOTE
If you don't see a Project window in the VB Editor, you can activate it by going up to the menu and selecting View ➪ Project Explorer. Alternatively, you can use the keyboard shortcut Ctrl+R.
The macro should look something like this:
Sub MyName() '' MyName Macro '' Keyboard Shortcut: Ctrl+Shift+N ActiveCell.FormulaR1C1 = "Michael Alexander" End Sub
The macro recorded is a Sub procedure that is named MyName. The statements tell Excel what to do when the macro is executed.
Notice that Excel inserted some comments at the top of the procedure. These comments are some of the information that appeared in the Record Macro dialog box. These comment lines (which begin with an apostrophe) aren't really necessary, and deleting them has no effect on how the macro runs. If you ignore the comments, you'll see that this procedure has only one VBA statement.
ActiveCell.FormulaR1C1 = "Michael Alexander"
This single statement causes the name you typed while recording to be inserted into the active cell.
Testing your macro
Before you recorded this macro, you set an option that assigned the macro to the Ctrl+Shift+N shortcut key combination. To test the macro, return to Excel by using either of the following methods:
Press Alt+F11.
Click the View Microsoft Excel button on the standard toolbar in the VB Editor window.
When Excel is active, activate a worksheet. (It can be in the workbook that contains the VBA module or in any other workbook.) Select a cell and press Ctrl+Shift+N. The macro immediately enters your name into the cell.
NOTE
In the preceding example, notice that you selected your target cell before you started recording your macro. This step is important. If you select a cell while the macro recorder is turned on, the actual cell that you selected will be recorded into the macro. In such a case, the macro would always format that particular cell, and it would not be a general-purpose macro.
Editing your macro
After you record a macro, you can make changes to it. For example, assume that you want your name to be bold. You could re-record the macro, but this modification is simple, so editing the code is more efficient. Press Alt+F11 to activate the VB Editor window. Then activate Module1
