The Macro Recorder | Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)

Earlier in this chapter, I discuss the macro recorder, which is a tool that converts your Excel actions into VBA code. This section covers the macro recorder in more detail.

Note	This is another reminder to make sure that Excel displays the Developer tab in the Ribbon. If you don't see this tab, refer to "Displaying Excel's Developer tab" earlier in this chapter.

The macro recorder is an extremely useful tool, but remember the following points:

The macro recorder is appropriate only for simple macros or for recording a small part of a more complex macro.
Not all the actions you make in Excel get recorded.
The macro recorder cannot generate code that performs looping (that is, repeating statements), assigns variables , executes statements conditionally, displays dialog boxes, and so on.
The macro recorder always creates Sub procedures. You cannot create a Function procedure by using the macro recorder.
The code that is generated depends on certain settings that you specify.
You'll often want to clean up the recorded code to remove extraneous commands.

What the macro recorder actually records

The Excel macro recorder translates your mouse and keyboard actions into VBA code. I could probably write several pages describing how this is done, but the best way to show you is by example. Follow these steps:

Start with a blank workbook.
Make sure that the Excel window is not maximized. You don't want it to fill the entire screen.
Press Alt+F11 to activate the VBE window.

Note
Make sure that this window is not maximized. Otherwise, you won't be able to see the VBE window and Excel's window at the same time.
Resize and arrange Excel's window and the VBE window so both are visible. (For best results, minimize any other applications that are running.)
Activate Excel, choose Developer Code Record Macro and then click OK to start the macro recorder.
Activate the VBE window.
In the Project Explorer window, double-click Module1 to display that module in the code window.
Close the Project Explorer window in the VBE to maximize the view of the code window.

Your screen layout should look something like the example in Figure 7-12. The size of the windows depends on your video resolution.

Figure 7-12: A convenient window arrangement for watching the macro recorder do its thing.

Now move around in the worksheet and select various Excel commands. Watch while the code is generated in the window that displays the VBA module. Select cells, enter data, format cells , use the Ribbon commands, create a chart, manipulate graphic objects, and so on. I guarantee that you'll be enlightened while you watch the code being spit out before your very eyes.

Relative or absolute?

When recording your actions, Excel normally records absolute references to cells. In other words, when you select a cell, it will remember that exact cell (not the cell relative to the current active cell ). To demonstrate how this works, perform these steps and examine the code:

Activate a worksheet and start the macro recorder.
Activate cell B1.
Enter Jan into cell B1.
Move to cell C1 and enter Feb .
Continue this process until you've entered the first six months of the year in B1:G1.
Click cell B1 to activate it again.
Stop the macro recorder and examine the new code in the VBE.

Excel generates the following code:

 Sub Macro1()     Range("B1").Select     ActiveCell.FormulaR1C1 = "Jan"     Range("C1").Select     ActiveCell.FormulaR1C1 = "Feb"     Range("D1").Select     ActiveCell.FormulaR1C1 = "Mar"     Range("E1").Select     ActiveCell.FormulaR1C1 = "Apr"     Range("F1").Select     ActiveCell.FormulaR1C1 = "May"     Range("G1").Select     ActiveCell.FormulaR1C1 = "Jun"     Range("B1").Select End Sub

To execute this macro, choose Developer Code Macros (or press Alt+F8) and select Macro1 (or whatever the macro is named) and click the Run button.

The macro, when executed, re-creates the actions that you performed when you recorded it. These same actions occur regardless of which cell is active when you execute the macro. Recording a macro using absolute references always produces the exact same results.

In some cases, however, you want your recorded macro to work with cell locations in a relative manner. For example, you'd probably want such a macro to start entering the month names in the active cell. In such a case, you want to use relative recording to record the macro.

You control how references are recorded by using the Developer Code Use Relative References button. This button is a toggle. When the button appears in a different color, the macro recorder records relative references. When the button appears in the standard color , the macro recorder records absolute references. You can change the recording method at any time, even in the middle of recording.

To see how this works, erase the cells in B1:D1 and then perform the following steps:

Activate cell B1.
Choose Developer Code Record Macro.
Click OK to begin recording.
Click the Use Relative Reference button to change the recording mode to relative.

After you click this button, it appears in a different color.
Enter the first six months' names in B1:G1, as in the previous example.
Select cell B1.
Stop the macro recorder.

With the recording mode set to relative, the code that Excel generates is quite different:

 Sub Macro2()     ActiveCell.FormulaR1C1 = "Jan"     ActiveCell.Offset(0, 1).Range("A1").Select     ActiveCell.FormulaR1C1 = "Feb"     ActiveCell.Offset(0, 1).Range("A1").Select     ActiveCell.FormulaR1C1 = "Mar"     ActiveCell.Offset(0, 1).Range("A1").Select     ActiveCell.FormulaR1C1 = "Apr"     ActiveCell.Offset(0, 1).Range("A1").Select     ActiveCell.FormulaR1C1 = "May"     ActiveCell.Offset(0, 1).Range("A1").Select     ActiveCell.FormulaR1C1 = "Jun"     ActiveCell.Offset(0, -5).Range("A1").Select End Sub

You can execute this macro by activating a worksheet and then choosing the Developer Code Macros command. Select the macro name and then click the Run button.

You'll also notice that I varied the procedure slightly in this example: I activated the beginning cell before I started recording. This is an important step when you record macros that use the active cell as a base.

Although it looks rather complicated, this macro is actually quite simple. The first statement simply enters Jan into the active cell. (It uses the active cell because it's not preceded by a statement that selects a cell.) The next statement uses the Select method (along with the Offset property) to move the selection one cell to the right. The next statement inserts more text, and so on. Finally, the original cell is selected by calculating a relative offset rather than an absolute cell. Unlike the preceding macro, this one always starts entering text in the active cell.

Note

You'll notice that this macro generates code that appears to reference cell A1 - which might seem strange because cell A1 was not even involved in the macro. This is simply a by-product of how the macro recorder works. (I discuss the Offset property later in this chapter.) At this point, all you need to know is that the macro works as it should.

The point here is that the recorder has two distinct modes, and you need to be aware of which mode you're recording in. Otherwise, the result will not be what you expected.

By the way, the code generated by Excel is more complex than it need be, and it's not even the most efficient way to code the operation. The macro that follows , which I entered manually, is a simpler and faster way to perform this same operation. This example demonstrates that VBA doesn't have to select a cell before it puts information into it - an important concept that can speed things up considerably.

 Sub Macro3()     ActiveCell.Offset(0, 0) = "Jan"     ActiveCell.Offset(0, 1) = "Feb"     ActiveCell.Offset(0, 2) = "Mar"     ActiveCell.Offset(0, 3) = "Apr"     ActiveCell.Offset(0, 4) = "May"     ActiveCell.Offset(0, 5) = "Jun" End Sub

In fact, this macro can be made even more efficient by using the With-End With construct:

 Sub Macro4()     With ActiveCell         .Offset(0, 0) = "Jan"         .Offset(0, 1) = "Feb"         .Offset(0, 2) = "Mar"         .Offset(0, 3) = "Apr"         .Offset(0, 4) = "May"         .Offset(0, 5) = "Jun"     End With End Sub

Or, if you're a VBA guru, you can impress your colleagues by using a single statement:

 Sub Macro5()     ActiveCell.Resize(,6)=Array("Jan","Feb","Mar","Apr","May","Jun") End Sub

Recording options

When you record your actions to create VBA code, you have several options in the Record Macro dialog box. The following paragraphs describe your options.

MACRO NAME

You can enter a name for the procedure that you are recording. By default, Excel uses the names Macro1 , Macro2 , and so on for each macro that you record. I usually just accept the default name and change the name of the procedure later. You, however, might prefer to name the macro before you record it. The choice is yours.

The Personal Macro Workbook

When you record a macro, one of your options is to record it to your Personal Macro Workbook. If you create some VBA macros that you find particularly useful, you might want to store these routines on your Personal Macro Workbook. This is a workbook named Personal.xlsb that is stored in your XLStart directory. Whenever you start Excel, this workbook is loaded, and you have access to the macros stored in the workbook. Personal.xlsb a hidden workbook, so it's out of your way when you're working in Excel.

The Personal.xlsb file doesn't exist until you record a macro to it.

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. Keep in mind that a shortcut key assigned to a macro overrides a built-in shortkey key (if one exists). For example, if you assign Ctrl+B to a macro, you won't be able to use the key combination to toggle the bold attribute in cells.

You can always add or change a shortcut key at any time, so you don't need to set this option while recording a macro.

STORE MACRO IN

The Store Macro In option tells Excel where to store the macro that it records. 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. (Read more about this in the sidebar, "The Personal Macro Workbook.")

Note	Excel remembers your choice, so the next time you record a macro, it defaults to the same location you used previously.

Cleaning up recorded macros

Earlier in this chapter, you see how recording your actions while you issue a single command (the Page Layout Page Setup Orientation command) produces an enormous amount of VBA code. This is an example of how, in many cases, the recorded code includes extraneous commands that you can delete.

About the Code Examples

Throughout this book, I present many small snippets of VBA code to make a point or to provide an example. Often, this code might consist of just a single statement. In some cases, the example consists of only an expression , which isn't a valid instruction by itself.

For example, the following is an expression:

 Range("A1").Value

To test an expression, you must evaluate it. The MsgBox function is a handy tool for this:

 MsgBox Range("A1").Value

To try out these examples, put the statement within a procedure in a VBA module, like this:

 Sub Test() ' statement goes here End Sub

Then put the cursor anywhere within the procedure and press F5 to execute it. Also, make sure that the code is being executed within the proper context. For example, if a statement refers to Sheet1 , make sure that the active workbook actually has a sheet named Sheet1 .

If the code is just a single statement, you can use the VBE Immediate window. The Immediate window is very useful for executing a statement "immediately" - without having to create a procedure. If the Immediate window is not displayed, press Ctrl+G in the VBE.

Just type the VBA statement in the Immediate window and press Enter. To evaluate an expression in the Immediate window, precede the expression with a question mark ( ? ). The question mark is a shortcut for Print . For example, you can type the following into the Immediate window:

 ? Range("A1").Value

The result of this expression is displayed in the next line of the Immediate window.

It's also important to understand that the macro recorder doesn't always generate the most efficient code. If you examine the generated code, you see that Excel generally records what is selected (that is, an object) and then uses the Selection object in subsequent statements. For example, here's what is recorded if you select a range of cells and then use some buttons on the Home tab to change the numeric formatting and apply bold and italic:

 Range("A1:C5").Select Selection.Style = "Comma" Selection.Font.Bold = True Selection.Font.Italic = True

The recorded VBA code works, but it's just one way to perform these actions. You can also use the more efficient With-End With construct, as follows:

 Range("A1:C5").Select With Selection     .Style = "#,##0.00"     .Font.Bold = True     .Font.Italic = True End With

Or you can avoid the Select method altogether and write the code even more efficiently , like this:

 With Range("A1:C5")      .Style = "#,##0.00"      .Font.Bold = True      .Font.Italic = True End With

If speed is essential in your application, you always want to examine any recorded VBA code closely to make sure that it's as efficient as possible.

You, of course, need to understand VBA thoroughly before you start cleaning up your recorded macros. But for now, just be aware that recorded VBA code isn't always the best, most efficient code.