Section 25.2. The Macro Recorder | Excel 2010: The Missing Manual

25.2. The Macro Recorder

The macro recorder is easy to use, but it's important to 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 menu selection 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 will ignore 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. For example, if you browse through the menu for 20 minutes before you eventually select File Print, Excel just adds a single line of VBA code to your macrothe line it needs to invoke the File 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.

25.2.1. Relative and Absolute Recording

When you click a button or make a menu selection, Excel's macro recorder knows exactly what you're doing. However, there are times when Excel needs a little guidance from you in order to decide how to interpret some of your actions. For example, 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 25.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 7.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.

The difference between the two modes is easiest to understand by following an example. Imagine you move to cell A1 and then start recording a macro. You then move two columns to the left 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, 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 is most often the case when you need 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 placesfor example, bolding, italicizing, and enlarging the font in a series of cells you select. It's the most common approach.

25.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 is attached to a workbook and saved in that workbook's .xls 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.xls, and you open another workbook namedMyMacroCollection.xls, which contains a few useful macros. You can use the macros contained in MyMacroCollection.xls with SalesReport.xls without a hitch. Once you close MyMacroCollection.xls, 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.
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.xls. The Personal.xls workbook opens automatically whenever you start Excel, so macros in this workbook are always available no matter what workbook you're using.

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.xls, which lives in a folder named XLStart. The exact location of XLStart depends on your version of Windows.

If you actually want to find the XLStart folder, you need to wade through your computer's hard drive. Some common locations include:

C\Windows\Application Data\Microsoft\XLStart
C:\Windows\Profiles\ UserName \Application Data\Microsoft\XLStart
C:\Documents and Settings\ UserName \Application Data\Microsoft\XLStart

( UserName indicates the account name of whoever's 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 is 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.xls 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 one of the common locations, 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 15 for more information about templates.

25.2.3. Recording a Macro

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

Select Tools Macros Record New Macro.

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

Figure 25-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 generic description indicating when the macro was created and who created it. Excel won't assign a shortcut key to the macro.

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). The box on Sidebar 25.3 gives more tips on picking keyboard shortcuts.
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, the Stop Recording toolbar appears (see Figure 25-2).

Figure 25-2. The floating Stop Recording toolbarwhich for some reason is always scrunched up like thisprovides two buttons : on the right side, a tiny grid that lets you set the cell reference mode, and on the left side, a square-stop button that allows you to end the macro recording session. In this example, the Relative Reference button is selected (and a border appears around the icon). If you ever forget whether you're in recording mode, just take a quick look at the status bar at the bottom of the Excel window; it displays the word "Recording" if the macro recorder is at work.

Decide whether you want to use absolute or relative references. Make sure that the Relative Reference button on the Stop Recording toolbar is selected if you want to use relative references. 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 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 25.2.1 for a full description of the difference between the two modes).

Note: It's possible to create a macro that performs some actions in relative reference mode, and some in absolute reference mode. All you need to do is 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 menu commands (like switching views, printing a document, creating a chart, and so on).

Tip: 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 menu, or a toolbar button. In all cases, the macro code is the same.
Click the Stop Recording button to save the macro.

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 status bar, which shows the word "Ready" instead of "Recording."

TROUBLESHOOTING MOMENT
What to Do if the Stop Recording Toolbar Disappears

It's all too easy to hide the Stop Recording toolbar. Unfortunately, if you accidentally make this mistake, Excel remains stuck in recording mode, with no obvious way for you to switch it off.

The short- term solution is to stop recording using the menu. Just choose Tools Macro Stop Recording. Excel promptly turns off the macro recorder (without recording this step) and saves the macro.

Unfortunately, this solution still leaves you with another problem. The next time you start recording a macro, the Stop Recording toolbar still doesn't appear. To show the Stop Recording toolbar, you need to select View Toolbars Customize while you're recording a macro . In the Toolbars tab, turn on the Stop Recording checkbox and click Close. The Stop Recording toolbar then springs back into view.

While you're at it you may also want to fire off a note to Microsoft's engineers asking them: Do you people work weekends making features like this difficult to use?

25.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 File New to create a new file. Just remember to keep your original workbook open if it contains the macro you want to use.
Choose Tools Macro Macros (or use the shortcut key Alt+F8).

The Macro dialog box appears, with a list of all the macros in the personal workbook and any open workbooks (see Figure 25-3). You can filter this list (to show, for example, only macros in the current workbook or those in the personal macro workbook) by choosing a different option from the "Macros in" list.
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 25-3. The Macro dialog box shows all the macros in every open workbook, as well as those in the personal workbook. In this example, there are 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 (MacroCollection.xls!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.

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 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're 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 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 might conflict with an Excel shortcut key. You can always assign one later if the macro proves extremely useful.

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

Macro

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