Recording a VBA Macro

   

By far, the easiest way to create a command macro is to use the Macro Recorder. With this method, you just start the recorder and then run through the operations you want to automate (which can include selecting text, running menu commands, and choosing dialog box options), and the Recorder translates everything into the appropriate VBA statements. These are copied to a separate area called a module , where you can then replay the entire procedure any time you like. This section shows you how to record a command macro in Word, Excel, or PowerPoint. (The other programs in the Office suite don't have macro recording capabilities.)

If you have Office 2003's personalized menus turned on, you may not see the Macro menu command. If not, click the arrow at the bottom of the menu to see all the commands. Once you've used the Macro command, it will appear in the Tools menu without having to display all the commands.

Here are the steps to follow:

1. Set up the application so that it's ready to record. In Word, for example, if you want to record a series of formatting options, select the text you want to work with.

2. Select Tools, Macro, Record New Macro. You'll see the Record Macro dialog box appear. Figure 1.1 shows the Excel version.

   Figure 1.1. Use the Record Macro dialog box to name and describe your macro.

   

3. The application proposes a name for the macro (such as Macro1), but you can use the Macro name text box to change the name to anything you like. However, you must follow a few naming conventions:

   • There can be no more than 255 characters . (That sounds like a lot, and it is. Since you'll often have to type macro names, I recommend keeping the names relatively short to save wear and tear on your typing fingers.)

   • The first character must be a letter or an underscore (_).

   • No spaces or periods are allowed.

4. In Word and Excel, assign an optional shortcut to the macro:

   • In Word, click Toolbars to assign a toolbar button to the macro; you can also click Keyboard to assign a shortcut key to the macro.

     

     Excel's Personal Macro Workbook is a hidden workbook (it's filename is Personal.xls ) that opens automatically when you start Excel. This is useful because any macros contained in this file will be available to all your workbooks, which makes them easy to reuse. If you want to use this workbook, however, you first have to unhide it. To do this, select the Window, Unhide command, make sure that PERSONAL.XLS is highlighted in the Unhide dialog box, and then click OK.

     
     

   • In Excel, enter a letter in the text box labeled Shortcut key: Ctrl+.

5. In Word, Excel, and PowerPoint, use the Store macro in the drop-down list to specify where the macro will reside:

   • In Word, you can store the macro in any open template (which makes the macro available to any document that uses the template), or in any open document (which makes the macro available only to that document).

   • In Excel, you can store the macro in the current workbook, a new workbook, or in the Personal Macro Workbook. If you use the Personal Macro Workbook, your macros will be available to all your workbooks.

   • In PowerPoint, you can store the macro in any open presentation.

6. Enter an optional description of the macro in the Description text box.

7. Click OK. The application returns you to the document, displays Recording or REC in the status bar, and displays the Stop Recording Macro toolbar, as shown in Figure 1.2. (This is the tiny toolbar shown more or less in the middle of the window, which shows only "St" in the title bar.)

   Figure 1.2. While you're recording, you see Recording or REC in the status bar as well as the Stop Recording Macro toolbar.

   

8. Perform the tasks you want to include in the macro. Because the macro recorder takes note of everything you do (except clicking the buttons in the Stop Recording Macro toolbar), be careful not to perform any extraneous actions or commands during the recording.

9. When you finish the tasks, select Tools, Macro, Stop Recording or click the Stop Macro button in the Stop Recording Macro toolbar.

Viewing the Resulting Module

When you record a macro, the application creates a "VBA project." This is a container that includes both the document you used for the macro and a special object called a module that contains the macro statements.

To see your macro, follow these steps:

1. Select Tools, Macro, Macros (or press Alt+F8) to display the Macro dialog box.

2. In the Macro name list, highlight the name of the macro you just recorded.

3. Click the Edit button. The application opens the Visual Basic Editor window and then opens the module and displays the macro. As you can see in Figure 1.3, the application (Excel, in this case) translates your actions into VBA statements and combines everything into a single macro.

   Figure 1.3. A sample recorded macro.

   

A typical macro has the following features:

Sub This keyword marks the beginning of a macro. The Sub keyword (it's short for subroutine ) is the reason why command macros also are called Sub procedures .

Macro Name After the Sub keyword, Excel enters the name of the macro followed by left and right parentheses (the parentheses are used for input valuesthe argumentsas you'll see in Chapter 2).

Comments The first few lines begin with an apostrophe ( ' ), which tells VBA that these lines are comments . As the name implies, comments are for show only; they aren't processed when you run the macro. In each recorded macro, the comments display the name of the macro and the description you entered in the Record Macro dialog box. (In Excel, the comments also display the keyboard shortcut, if you entered one.)

Macro statements The main body of the macro (in other words, the lines between Sub and End Sub , not including the initial comments) consists of a series of statements. These are the application's interpretations of the actions you performed during the recording. In the example, four actions were performed in Excel:

1. Cell B2 was selected:

    Range("B2").Select 

2. The cell was formatted as boldface:

    Selection.Font.Bold = True 

3. The word "Expenses" was typed into the cell:

    ActiveCell.FormulaR1C1 = "Expenses" 

4. The right arrow key was pressed (which moved the selection over to cell C2):

    Range("C2").Select 

End Sub These keywords mark the end of the macro.

Editing a Recorded Macro

As you're learning VBA, you'll often end up with recorded macros that don't turn out quite right the first time. Whether the macro runs a command it shouldn't or is missing a command altogether, you'll often have to patch things up after the fact.

The lines within a VBA module are just text, so you make changes the same way you would in a word processor or text editor. If your macro contains statements that you want to remove, just delete the offending lines from the module.

If you want to add new recorded actions to the macro, VBA doesn't give you any way to record new statements into an existing macro. Instead, you should first record a new macro that includes the actions you want and then display the macro. From there, you can use the standard Windows cut-and-paste techniques (including drag-and-drop) to move the statements from the new macro into the other macro.

The Absolute Minimum

This chapter introduced you to VBA macros. You learned what macros are and why they're useful. You also learned how to record a macro, see the result in the Visual Basic Editor, and edit a recorded macro.

The few VBA statements that you've seen so far probably look more than a little strange to you. Yes, recorded statements correspond to specific actions you took while the Macro Recorder was running, but the resulting code contains weird constructions, such as Selection.Font.Bold and ActiveCell.FormulaR1C1. It's perfectly normal to feel a bit intimidated by these barely decipherable statements. My job, over the next five chapters, in particular, will be to dispel the inherent strangeness of VBA and show you that the language is actually quite comprehensible once you learn what it's all about. To that end, you'll find related information in the following chapters:

• You'll learn more about the Visual Basic Editor as well as how to create you own procedures and enter your own VBA statements in Chapter 2, "Writing Your Own Macros."

• You won't get too far writing VBA code without learning about variables, and you'll do that in Chapter 3, "Understanding Program Variables ."

• Your procedures will also rely heavily on operators and expressions. Turn to Chapter 4, "Building VBA Expressions," to learn more.

• Objects are one of the most important concepts in VBA. You'll find out how they work in Chapter 5, "Working with Objects." Also, see Part II, "Putting VBA to Work," to get the specifics on the objects used in Word, Excel, and other Office applications.

• VBA, like any programming language worth its salt, contains a number of statements that control program flow. I discuss these statements in Chapter 6, "Controlling Your VBA Code."