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.
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.
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:
( 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.
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).
|
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).
|
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).
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).
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? |
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.
|
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:
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. |