To see how the recorder works, try creating a simple macro that inserts a company name and address in a worksheet. Follow these steps:
In the Macros group on the View tab, click Macros and then Record Macro. Excel displays the Record Macro dialog box shown in Figure 27-1.
Assign a name to the macro. You can accept the suggestion (Macro1) or type your own name. Let’s use CompanyAddress. (The macro recorder doesn’t permit space characters.)
Assign a key combination to the macro by typing a letter in the text box. If possible, use something related to the purpose of the macro so you’ll remember it later. Let’s use uppercase A. (The recorder distinguishes capital letters from lowercase ones.)
Accept the default of This Workbook for Store Macro In. (We’ll discuss the Personal Macro Workbook option later in this chapter.)
Type a description for the macro in the Description box. Enter company address will do nicely.
To begin recording, click OK. A Stop Recording button appears on the status bar.
Select A6, and type Coho Winery. In A7, type 3012 West Beaujolais St., and in A8, type Walla Walla, WA 98765.
Click Stop Recording on the status bar.
To test the new macro, clear the worksheet, and then press Ctrl+Shift+A. Excel runs the macro and performs the sequence of actions in the same way you recorded them.
If you forget the keyboard shortcut for a macro, or if you didn’t bother to assign one, you can run your macro by clicking Macros and then View Macros in the Macros group on the View tab. In the Macro dialog box that appears, shown in Figure 27-2, you can select a macro and run it. You can also click the Edit button in the Macro dialog box to visit (and alter) the code, or click the Options button to assign the shortcut you neglected to assign earlier.
The macro you just recorded has one serious fault (other than it doesn’t create your company address): It always does its business in cells A6:A8. It would be considerably more valuable if you could use it anywhere.
By default, the macro recorder records absolute references. That is, if you’re in A6 when you record a cell entry, the action will play back in A6. To make it record relative references instead, click Macros and then Use Relative References in the Macros group on the View tab.
The Use Relative References button is a toggle. If you’re using relative references and you click this button again, the recorder returns to absolute referencing. You can toggle between the two modes as often as you like while you are recording—which means your recorded macros can contain whatever combination of absolute and relative references suits your purposes.
Caution
When clicked, the Use Relative References command does not change to say Use Absolute References, as you might expect. Instead, it appears highlighted on the ribbon when you’re recording (or set to record) relatively, but it is not highlighted when you’re recording absolutely. To avoid disappointment, be sure to verify the state of this toggle when you begin recording.
The most likely reason a recorded macro might not generate the expected result is that it was recorded in the wrong relative/absolute state. If output from your macro does not appear in the worksheet location where you want it, consider the possibility that you recorded it with absolute instead of relative references, and try recording it again.
A second common cause for disappointment is that the macro recorder records completed actions only. An example will illustrate: Suppose you want to record a macro that places a full date and time stamp in the current cell. You know you can press Ctrl+; to generate the current date and Ctrl+: to generate the current time. But you want the date and the time in the same cell, and Excel has no keyboard shortcut for that. A macro can help.
One way to achieve the date and time stamp (without a macro) is to type =NOW() and then press F9 before pressing Enter. The NOW function returns the full date and time, and pressing F9 before pressing Enter converts the formula to its calculated result.
You might suppose you could turn on the macro recorder, set references to relative, and then record exactly those steps: =NOW(), F9. Unfortunately, the result of your work would be a macro that always entered the date and time of its creation, not the current date and time. That’s because the macro recorder “sees” a sequence such as this as a single action, even though it might seem like two separate steps to you.
The solution to this problem is to find another way to carry out the action that really does involve discrete steps. You can accomplish that by typing the formula, pressing Enter, selecting the cell in which you typed the formula, copying it to the Clipboard, and then using the Paste Special command and selecting Values in the Paste Special dialog box. That’s the long way around when you want to convert a formula to its result immediately, but it works for the macro recorder.
Further troubleshooting of recorded macros requires a rudimentary understanding of VBA. If you like the convenience and enhanced productivity that macros afford, you will probably find it worthwhile to look at the code that the macro recorder generates and learn a bit about how that code works. If you’re new to VBA, the following sections will help you get started.