Section 27.2. The Macro Recorder | Excel 2007[c] The Missing Manual

27.2. The Macro Recorder

The macro recorder is easy to use, but keep a few key points in mind while you're recording so that you end up with a great collection of really useful tools:

Excel is watching you . Excel captures every ribbon command or keyboard shortcut you use. In other words, don't do anything that you don't want recorded as part of the macro, unless you're willing to edit the VBA macro code after the fact. Also, try to avoid switching to another program while you're recording an Excel macro. Even though the macro recorder ignores anything you do outside of Excel, it's easy to confuse yourself and inadvertently add macro code you don't want by jumping back and forth.
You don't need to work fast . The macro recorder doesn't record anything in between each action you perform. If you browse through the ribbon for 20 minutes before you eventually select Office button Print, Excel just adds a single line of VBA code to your macrothe line it needs to invoke the Office button Print command. As a result, macros tend to execute very quickly when you replay themmuch more quickly, in fact, than when you perform the actions yourself.
Try to be generic . The ideal macro is general enough that you can reuse it in a wide range of scenarios. If you make a macro so specific that you can use it only once, the macro won't get much use, and all your hard work will be wasted .

Before you jump into the macro recording studio, it helps to understand the difference between recording modes and to know where Excel saves your macros. Those topics are covered in the next two sections.

27.2.1. Relative and Absolute Recording

When you click a command in the ribbon, Excel's macro recorder knows exactly what you're doing. However, sometimes Excel needs a little guidance from you in order to decide how to interpret some of your actions. When you type in text, Excel can interpret your action in two different ways, depending on which of two recording modes you're in (Section 27.2.3 tells you how to change modes):

Absolute reference mode . In absolute reference mode, Excel stores the absolute references for the cells that you're modifying (for a refresher on absolute cell references, see Section 8.3.1). When you play the macro again, the macro affects only these cells.
Relative reference mode . In relative reference mode, Excel tracks how far you move from your starting position. That means when you play the macro again, Excel takes your current location into account.

You can most easily understand the difference between the two modes by following an example. Imagine you move to cell A1 and then start recording a macro. You then move two columns to the right to cell C1, type in the number 42, and save the macro.

If you used absolute reference mode, here's the series of instructions that Excel stores in your macro:

Move to cell C1 .
Enter the number 42 .

On the other hand, if you used relative reference mode, Excel stores this list of instructions:

Move two columns to the right (from wherever the active cell is) .
Enter the number 42 .

You'll see the difference if you clear the worksheet, move to cell E10, and play the macro. If you used absolute reference mode when you recorded the macro, then the macro always returns to cell C1 to enter its information. If you used relative reference mode, Excel enters the number 42 two cells to the right of the current cell, in cell G10.

Tip: Absolute reference mode works if your data always needs to be in the same position. This need arises most often when you have to add some sort of header information at the top of a worksheet. Relative reference mode works if you need to repeat a task in several placeslike bolding, italicizing, and enlarging the font in a series of cells you select. It's the most common approach.

27.2.2. Where Macros Live

Once you've crafted the perfect macro, you need to make sure Excel stores it so that you can find it later on. In Excel, every macro's attached to a workbook and saved in that workbook's .xlsm file. When you open a workbook that contains a set of macros, Excel makes them available instantly.

Excel beginners often assume that before you can use a macro in a particular workbook, you need to store the macro inside that workbook. In fact, macros have a much greater range. As soon as you open a workbook that contains any macros, Excel makes those macros available to every other workbook that's currently open.

In other words, imagine you're editing a workbook named SalesReport.xlsx, and you open another workbook named MyMacroCollection.xlsm, which contains a few useful macros. You can use the macros contained in MyMacroCollection.xlsm with SalesReport.xlsx without a hitch. Once you close MyMacroCollection.xlsm, those macros are no longer available.

This design makes it easy to share and reuse macros across workbooks (and between different people). It's up to you whether you want to store macros in your most commonly used workbooks or create standalone workbooks that hold collections of useful macros.

When you record a macro, Excel gives you three slightly different storage options:

This Workbook . If you choose this option, Excel stores your macro in the current workbook. Remember, you need to save this workbook as a macro-enabled .xlsm file or a binary .xlsb file, or you'll lose your macros.
New Workbook . If you choose this option, Excel automatically creates a new workbook (which it opens in a separate window) and stores your macro there.
Personal Macro Workbook . If you choose this option, Excel stores your macro in a special hidden workbook named Personal.xlsb. The Personal.xlsb workbook opens automatically whenever you start Excel (although it remains hidden), so macros in this workbook are always available no matter what workbook you're using.

Note: If you try to save a workbook that contains a macro using Excel's macro-free .xlsx file format, you receive an error message warning you that you'll lose your macros. Instead, choose Office button

Save As, and then, from the "Save as type list, pick "Excel Macro Enabled Workbook (*.xlsm)".

FREQUENTLY ASKED QUESTION
Locating the Personal Macro Workbook

Where's the personal macro workbook hidden?

It makes sense to save your most useful macros in your personal macro workbook, so that they're always available, without requiring any extra steps. Excel stores the personal macro workbook in a file called Personal.xlsb, which lives in a folder named XLStart. You can find the XLStart folder in a location like C:\Documents and Settings\UserName\Application Data\Microsoft\XLStart, where UserName indicates the account name of whoever is currently logged on to Windows.

The XLStart folder is usually right next to your templates folder. In other words, if your templates folder is located at C:\Documents and Settings\BillJones\Application Data\Microsoft\Templates , your XLStart folder's C:\Documents and Settings\BillJones\Application Data\Microsoft\XLStart .

Each person with an account on your PC has a separate personal workbook, so if you log on as someone else, you won't have the same collection of macros. Also, keep in mind that Excel doesn't actually create the personal macro workbook until you add your first macro to it. So, unless you've recorded at least one macro for the personal macro workbook to hold, there's no point hunting for it.

If you're in the habit of placing a lot of important macros in the Personal.xlsb file, you should search it out. That way, you can easily back it up so that you don't lose all your hard work when the next computer virus strikes. If all else fails and you can't locate the personal macro workbook in the location where you expect to find it, try using the Search command in the Windows Start menu.

Just because you place a macro in a specific location doesn't mean it needs to stay there. In fact, it's quite easy to copy a macro from one workbook to another, or even move it into and out of the personal macro workbook. All you have to do is fire up the Visual Basic editor and learn a little bit about how it organizes macro code. You'll get an introduction to the editor in the next chapter.

Tip: One useful place to put macros is in an Excel template. That way, every time you or anyone else creates a new workbook based on the template, you have immediate access to the macros. In order to put macros in a template, record them inside the template using the This Workbook option. See Chapter 16 for more information about templates.

27.2.3. Recording a Macro

Now that you've learned the basics of Excel macros, it's time to try creating one:

Choose View Macros Record Macro .

The Record Macro dialog box appears (Figure 27-1).

Figure 27-1. Here, Excel is about to start recording a macro named InsertHeader. Excel will store this macro in the current workbook along with an optional description. Since the "Shortcut key" box has been left blank, Excel won't assign a shortcut key to the macro.

Tip: For an even faster way to start recording a new macro, click the macro recording button in the Status bar. It looks like a worksheet window with a red circle (representing a record button) superimposed on top. If you don't see the button, right-click the Status bar and make sure the Macro Record item's checked.

Type in a name for your macro .

The macro name can include letters , numbers , and the underscore character (_), but it can't include spaces or other special characters , and it must begin with a letter. Use a descriptive macro name like CompanyHeader or CreateSalesChart, because you'll identify the macro based on the name later on. (The macro name is also the name Excel uses for the subroutine where it records all the VBA instructions.)

If you want to create a keyboard shortcut, choose one now .

Shortcut keys let you launch your macros in a hurry. The only shortcut keys you can use are Ctrl+ letter combinations. You can use uppercase or lowercase letters, as in Ctrl+A (which means hold down the Ctrl key and press A) and Ctrl+Shift+A (which means hold down the Ctrl and Shift keys, and press A).

WORD TO THE WISE
The Dangers of Macro Shortcuts

Using shortcut keys can be dangerous because Excel doesn't warn you if you choose a shortcut key that already corresponds to another Excel task.

If this sort of conflict occurs, Excel always uses the shortcut key for the macro . This habit can cause confusion if other people use your macros and you replace a common shortcut key. Imagine their surprise when they hit Ctrl+S to save a document and end up triggering a macro that turns all negative numbers positive.

Here are some common key combinations that you should never assign to macro shortcuts because people use them too frequently:

Ctrl+S (Save)
Ctrl+P (Print)
Ctrl+O (Open)
Ctrl+N (New)
Ctrl+X (Exit)
Ctrl+Z (Undo)
Ctrl+Y (Redo/Repeat)
Ctrl+C (Copy)
Ctrl+X (Cut)
Ctrl+V (Paste)

To avoid problems, always use Ctrl+Shift+ letter macro key combinations, because these combinations are much less common than the Ctrl+ letter shortcut keys. And if you're in doubt, don't assign a shortcut key when you create a new, untested macro, especially if you think it may conflict with an Excel shortcut key. You can always assign one later if the macro proves extremely useful.

Choose a storage location from the "Store macro in" list .

This option determines where Excel saves the macro.
In the Description text box, type a description for the macro .

You can use the description to help you identify macros later on. Excel also adds the description to the macro code as a series of comments.
Click OK to start recording the macro .

At this point, macro recording begins. If the red record circle is visible on the Status bar, it changes to a blue stop square (Figure 27-2).

Decide whether you want to use absolute or relative references. If you want to use relative references, make sure the View Macros Use Relative References buttons selected. If you want to use absolute references, make sure it's not selected .

Remember, if you use absolute references, Excel always replays your actions on the same cells. If you use relative references, Excel executes your actions according to where you are at the time you launch the macro (see Section 27.2.1 for a full description of the difference between the two modes).

Figure 27-2. The stop button (in the shape of a square) in a spreadsheet's lower-left corner is a visual indication that you're in record mode. If you don't see the macro stop button in the Status bar, then you can switch it on by right-clicking the Status bar, and then, in the popup menu, choosing Macro Recording.

Note: It's possible to create a macro that performs some actions in relative reference mode, and some in absolute reference mode. You just click the Relative Reference button on or off while you're recording the macro. However, this approach can lead to much confusion, so unless you're very comfortable with macros, you'll probably want to pick one system before you start recording, and then stick with it.

Perform the actions you want Excel to record .

The macro recorder records all the actions you take, from worksheet editing and formatting to ribbon commands (like switching views, printing a document, creating a chart, and so on).

Note: The macro recorder stores code, not your specific actions. That means it doesn't matter whether you activate a feature using a shortcut key, the Quick Access toolbar, or the ribbon. In all cases, the macro code is the same.
Choose View Macros Stop Recording button to save the macro (or click the blue stop square in the Status bar) .

Excel stops recording and quietly stores your macro. Excel doesn't show any confirmation messagethe only way you can tell that the macro recorder has switched itself off is by looking at the ribbon or Status bar, where the stop button is replaced by the record button.

27.2.4. Playing a Macro

Once you've recorded a macro, it's even easier to play it back. Just follow these steps:

Move to the location where you want to play the macro .

The location doesn't make a difference if your macro doesn't modify the worksheet, or if it uses absolute reference mode. On the other hand, if your macro uses relative reference mode and it modifies or formats cells, it's important to position yourself where you want to apply the changes.

If you want to test your macro in a completely new workbook, select Office button New to create a new file. Just remember to keep your original workbook open if it contains the macro you want to use.
Macros View Macros (or click the green play button in the Status bar). Keyboard lovers can also use the handy Alt+F8 shortcut.
The Macro dialog box appears, with a list of all the macros in the personal workbook and any open workbooks (see Figure 27-3). You can filter this list (to show things like only macros in the current workbook or those in the personal macro workbook) by choosing a different option from the "Macros in" list.

Note: If you get an error message informing you that Excel has turned off the macros in your workbook (which it does automatically when you close and reopen it), you need to take a few extra steps. The next section has the scoop on macro security.
Select the macro from the list and click Run .

Excel closes the Macro dialog box and runs the macro. Don't blink99 percent of all macros replay so quickly that you can't see the changes being made. Instead, the modifications seem to appear all at once.

Note: Once you play a macro, you're stuck with the changes. Sadly, the Undo feature can't reverse a macro, so make sure you save your worksheet before trying out an untested macro.

Figure 27-3. The Macro dialog box shows all the macros in every open workbook, as well as those in the personal workbook. This example has two available macros. The first one, InsertHeader, is located in another workbook. Excel uses a name that starts with the name of the workbook file to indicate this fact (MacroCollection.xlsm!InsertHeader). The second macro, PrintCustom, is in the current workbook, and so the file name isn't present. The Macro dialog box also includes a Delete button that lets you remove a macro, and several additional buttons for editing and debugging macro code, which you'll learn about in the next chapter.

Tip: If you created a shortcut key for your macro, you can trigger the macro just by pressing the appropriate key combination, without using the Macro dialog box. Also, you can attach a shortcut key to an already created macro. Just select View

Macros

View Macros, and then select the macro you want to modify. Click Options, and then enter (or change) the shortcut key.