In This Chapter
Recording your actions using the Excel built-in macro recorder Understanding the types of macros you can record Setting the appropriate options for macro recording
You can use two methods to create a macro:
Record it using the Excel macro recorder
Write it with VBA
This chapter deals specifically with the ins and outs of using the Excel macro recorder. Recording a macro isn’t always the best approach, and some macros simply can’t be recorded, no matter how hard you try. You see, however, that the Excel macro recorder is very useful. Even if your recorded macro isn’t quite what you want, the recorder is an excellent learning tool.
Is It Live or Is It VBA?
Recording a macro is sort of like using a tape recorder. Turn it on, do your thing, and then turn it off. This analogy, however, goes only so far. Table 6-1 compares tape recording with macro recording.
| Table 6-1 Tape Recording versus Macro Recording | ||
| Tape Recorder | Excel Macro Recorder | |
| What equipment is required? | A tape recorder and a microphone. | A computer and a copy of Excel. |
| What is recorded? | Sounds. | Actions taken in Excel. |
| Where is the recording stored? | On magnetic tape. | In a VBA module. |
| How do you play it back? | Rewind the tape and press Play. | Choose ToolsO MacroOMacros (or other methods). |
| Can you edit the recording? | Yes, if you have the proper equipment. | Yes, if you know what you’re doing. |
| Can you copy the recording? | Yes, if you have a second tape recorder. | Yes (no additional equipment required). |
| Is the recording accurate? | Depends on the situation and the equipment quality. | Depends on how you set things up. |
| What if you make a mistake? | Rerecord the tape (or edit it if possible). | Rerecord the macro (or edit it if possible). |
| Can you view the recording? | No, it’s just a bunch of magnetic impulses. | Yes, by activating a VBA module. |
| Can you make money with the recording? | Yes, if it’s good (editing usually required). | Yes, but you need to do a lot of editing first. |
Recording Basics
You take the following basic steps when recording a macro. I describe these steps in more detail later in this chapter.
1. Determine what you want the macro to do.
2. Get things set up properly.
This step determines how well your macro works.
3. Determine whether you want cell references in your macro to be relative or absolute.
4. Choose Tools Macro Record New Macro. Excel displays its Record Macro dialog box.
5. Enter a name, shortcut key, macro location, and description. Each of these items — with the exception of the name — is optional.
6. Click OK in the Record Macro dialog box.
Excel automatically inserts a VBA module. From this point, Excel converts your actions into VBA code. It also displays a miniature floating toolbar, which contains two toolbar buttons: Stop Recording and Relative Reference.
7. Perform the actions you want recorded using the mouse or the keyboard.
8. After you’re finished, click the Stop Recording button on the miniature toolbar (or choose ToolsOMacroOStop Recording).
Excel stops recording your actions
9. Test the macro to make sure it works correctly.
The macro recorder is best suited for simple, straightforward macros. For example, you might want a macro that applies formatting to a selected range of cells or that sets up row and column headings for a new worksheet.
The macro recorder is for Sub procedures only. You can’t use the macro recorder to create Function procedures.
You may also find the macro recorder helpful for developing more-complex macros. Often, I record some actions and then copy the recorded code into another, more complex macro. In most cases, you need to edit the recorded code and add some new VBA statements.
The macro recorder cannot generate code for any of the following tasks, which I describe later in the topic:
Performing any type of repetitive looping
Performing any type of conditional actions (using an If-Then statement) Assigning values to variables Specifying data types Displaying pop-up messages Displaying custom dialog boxes
The macro recorder’s limited capability certainly doesn’t diminish its importance. I make this point throughout the topic: Recording your actions is perhaps the best way to learn VBA. When in doubt, try recording. Although the result may not be exactly what you want, viewing the recorded code might steer you in the right direction.
Preparing to Record
Before you take the big step and turn on the macro recorder, spend a minute or two thinking about what you’re going to do. You record a macro so that Excel can automatically repeat the actions you record.
Ultimately, the success of a recorded macro depends on five factors:
How the workbook is set up while you record the macro
What is selected when you start recording
Whether you use absolute or relative recording mode
The accuracy of your recorded actions
The context in which you play back the recorded macro
The importance of these factors becomes crystal clear when I walk you through an example.
Relative or Absolute?
When recording your actions, Excel normally records absolute references to cells. (This is the default recording mode.) Very often, this is the wrong recording mode. If you use relative recording, Excel records relative references to cells. The distinction is explained in this section.
Recording in absolute mode
Follow these steps to record a simple macro in absolute mode. This macro simply enters three month names into a worksheet:
1. Choose Tools Macro Record New Macro.
2. Type Absolute as the name for this macro.
3. Click OK to begin recording.
4. Activate cell B1 and type Jan in that cell.
5. Move to cell C1 and type Feb.
6. Move to cell D1 and type Mar.
7. Click cell B1 to activate it again.
8. Stop the macro recorder.
9. Press Alt+F11 to activate the VBE. 10. Examine the Modulel module.
Excel generates the following code:
When executed, this macro selects cell B1 and inserts the three month names in the range B1:D1. Then the macro reactivates cell B1.
These same actions occur regardless of which cell is active when you execute the macro. A macro recorded using absolute references always produces the same results when it is executed. In this case, the macro always enters the names of the first three months into the range B1:D1.
Recording in relative mode
In some cases you want your recorded macro to work with cell locations in a relative manner. You may want the macro to start entering the month names in the active cell. In such a case, you need to use relative recording.
The Stop Recording toolbar, which consists of only two buttons, is displayed when you are recording a macro. You can change the manner in which Excel records your actions by clicking the Relative Reference button on the Stop Recording toolbar. This button is a toggle. When the button appears in a pressed state, the recording mode is relative. When the button appears normally, you are recording in absolute mode.
You can change the recording method at any time, even in the middle of recording.
To see how relative mode recording works, erase the cells in B1:D1 and then perform the following steps:
1. Activate cell B1.
2. Choose Tools Macro Record New Macro.
3. Name this macro Relative.
4. Click OK to begin recording.
5. Click the Relative Reference button to change the recording mode to relative.
When you click this button, it looks pressed.
6. Activate cell B1 and type Jan in that cell.
7. Move to cell C1 and type Feb.
8. Move to cell D1 and type Mar.
9. Select cell B1.
10. Stop the macro recorder.
Notice that this procedure differs slightly from the previous example. In this example, you activate the beginning cell before you start recording. This is an important step when you record macros that use the active cell as a base.
This macro always starts entering text in the active cell. Try it. Move the cell pointer to any cell and then execute the Relative macro. The month names are always entered beginning at the active cell.
With the recording mode set to relative, the code Excel generates is quite different from absolute mode:
To test this macro, activate any cell except B1. The month names are entered in three cells, beginning with the cell that you activated.
Notice that the code generated by the macro recorder refers to cell A1. This may seem strange because you never used cell A1 during the recording of the macro. This is simply a byproduct of the way the macro recorder works. (I discuss this in more detail in Chapter 8 where I talk about the Offset method.)
What Gets Recorded?
When you turn on the macro recorder, Excel converts your mouse and keyboard actions into valid VBA code. I could probably write several pages describing how Excel does this, but the best way to understand the process is by watching the macro recorder in action. (Figure 6-1 shows how my screen looked while I had the macro recorder turned on.)
Follow these steps:
1. Start with a blank workbook.
2. Make sure that the Excel window is not maximized.
3. Press Alt+F11 to activate the VBE (and make sure that this program window is not maximized).
4. Resize and arrange the Excel window and the VBE window so that both are visible.
For best results, position the Excel window on top of the VBE window, and minimize any other applications that are running.
5. Activate Excel and choose Tools Macro Record New Macro.
6. Click OK to start the macro recorder.
Excel inserts a new module (named Module1) and starts recording in that module
7. Activate the VBE program window.
8. In the Project Explorer window, double-click Module1 to display that module in the Code window.
Now play around for a while: Choose various Excel commands and watch the code being generated in the VBE window. Select cells, enter data, format cells, use the menus and toolbars, create a chart, manipulate graphics objects, and so on — go crazy! I guarantee that you’ll be enlightened as you watch Excel spit out the VBA code before your very eyes.
Figure 6-1:
A convenient window arrangement for watching the macro recorder do its thing.
Recording Options
When recording your actions to create VBA code, you have several options. Recall that the Tools Macro Record New Macro command displays the Record Macro dialog box before recording begins, as shown in Figure 6-2.
The Record Macro dialog box, shown in Figure 6-2 gives you quite a bit of control over your macro. In the following sections, I describe these options.
Figure 6-2:
The Record Macro dialog box provides several options.
Macro name
You can enter a name for the Sub procedure that you are recording. By default, Excel uses the names Macro1, Macro2, and so on for each macro you record. I usually just accept the default name. If the macro works correctly and I want to save it, I give it a more descriptive name later on. You, however, may prefer to name the macro upfront — the choice is yours.
Shortcut key
The Shortcut key option lets you execute the macro by pressing a shortcut key combination. For example, if you enter w (lowercase), you can execute the macro by pressing Ctrl+w. If you enter W (uppercase), the macro comes alive when you press Ctrl+Shift+W.
You can add or change a shortcut key at any time, so you don’t have to set this option when recording a macro. See Chapter 5 for instructions on assigning a shortcut key to an existing macro.
Store Macro In
The Store Macro In option tells Excel where to store the macro that it is recording. By default, Excel puts the recorded macro in a module in the active workbook. If you prefer, you can record it in a new workbook (Excel opens a blank workbook) or in your Personal Macro Workbook.
Your Personal Macro Workbook is a hidden workbook that opens automatically when Excel starts. This is a good place to store macros that you’ll use with multiple workbooks. The Personal Macro Workbook is named personal. xls and it is created the first time you specify it as the location for a recorded macro.
Description
When you record a macro, the macro begins with five comment lines (three of them blank) that list the macro name, the user’s name, and the date. You can put anything you like here or nothing at all. As far as I’m concerned, the Description option is a waste of time because I always end up deleting these lines in the module.
Is This Thing Efficient?
You might think that recording a macro would generate some award-winning VBA code — better than you could ever write manually. Think again. In many cases, the recorder spits out lots of extraneous garbage, and it often generates code that’s less than efficient.
Don’t get me wrong. I’m a staunch supporter of the macro recorder. It’s a great tool for helping you learn VBA. Except for simple macros, however, I’ve never used a recorded macro without fixing it up a bit (usually quite a bit).
To demonstrate just how inefficient the macro recorder’s code can be, try this:
1. Turn on the macro recorder.
2. Choose File Page Setup.
The Page Setup dialog box appears.
3. Make sure the Page tab is selected.
4. Change the page orientation to Landscape and click OK.
5. Turn off the macro recorder.
To take a look at the macro, activate the Module1 sheet. This single — and very simple — command generates the following code:
You may be surprised by the amount of code generated by this single command. (I was, the first time I tried something like this.) Although you changed only one setting in the Page Setup dialog box, Excel generated code that reproduces all the settings in the dialog box.
This is a good example of macro-recording overkill. If you want a macro that simply switches the page setup to landscape mode, simplify this macro considerably by deleting the extraneous code. This makes the macro faster and easier to read. You can simplify this macro as follows:
I deleted all the code except the line that sets the Orientation property. Actually, you can simplify this macro even more because you don’t really need the With.End With construct:
In this case, the macro changes the Orientation property of the PageSetup object on the active sheet. All other properties are unchanged. By the way, xlLandscape is a built-in constant that makes things easier. I discuss built-in constants in Chapter 7.
Rather than record this macro, you could enter it directly into a VBA module. To do so, you have to know which objects, properties, and methods to use. Although the recorded macro isn’t all that great, by recording it you realize that the PageSetup object has an Orientation property. This example shows how the macro recorder can help you learn VBA.
This chapter nearly sums it up when it comes to using the macro recorder. The only thing missing is experience. Eventually, you discover which recorded statements you can safely delete. Better yet, you discover how to modify a recorded macro to make it more useful.
