Recording a VBA Macro

As I mentioned earlier, only Word and Excel come with the Record Macro command, which is a shame. (In Office 2003, you could record macros in PowerPoint, too.) However, these two programs are the most suited to recording macros, so it's not all that surprising that Microsoft has restricted the Office macro recording capabilities. The next two sections show you how to record a macro in Word and Excel.

Recording a Word Macro

Before getting started, make sure that Word is set up so that it's ready to record. If you want to perform your actions on a specific document, for example, make sure that document is open. Similarly, if you want to record a series of formatting options, select the text you want to work with. Here are the steps to follow to record a macro in Word:

1. Choose View, pull down the Macros menu, and then choose Record Macro. (You can also click the Macro Recording button in the status bar. If you don't see the Macro Recording button, right-click the status bar and then click Macro Recording.) The Record Macro dialog box appears, as shown in Figure 1.1.

Figure 1.1

Use Word's version of the Record Macro dialog box to name and describe your macro.

2. Word proposes a name for the macro (such as Macro1), but you should use the Macro Name text box to change the name to something more meaningful. However, you must follow a few naming conventions:

• No more than 255 characters. (That sounds like a lot, and it is. Because you may occasionally 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.

You'll see later on that one way to run a recorded macro is to select it from a list of all your recorded macros. If you create a lot of macros this way,that list will get long in a hurry.Therefore, when naming your recorded macros, make sure you assign names that will make it easy to differentiate one macro from another. Names such as Macroi and Macro2 tell you nothing,but names such as AdjustwindowSize and NewDocumentTasks are instantly understandable.

3. Use the Store Macro In drop-down list to specify where the macro will reside. I recommend keeping the default All Documents (Normal.dotm) option. This saves the macro in the Normal template, which makes it available all the time. (You can also 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.)

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

5. Click OK. The application returns you to the document and starts the recorder.

6. Perform the tasks you want to include in the macro. Here are some things to bear in mind during the recording:

• Word gives you two indications that a recording is in progress (see Figure 1.2): the mouse pointer includes what looks like a cassette tape icon, and the status bar's Record Macro button changes to a blue square.

• The mouse works only for selecting Ribbon commands and dialog box options. If you need to change the document cursor position or select text, you need to use the keyboard.

• Because the macro recorder takes note of everything you do, be careful not to perform any extraneous keyboard actions or mouse clicks during the recording.

Figure 1.2

Word indicates that you're recording a macro by changing the mouse pointer and Record Macro button.

Figure 1.2

Word indicates that you're recording a macro by changing the mouse pointer and Record Macro button.

Macro Recording Pointer

Record Macro button

Mouse pointer

Record Macro button

Mouse pointer

7. When you finish the tasks, choose View, pull down the Macros menu, and then choose Stop Recording (or click the Macro Recording button in the status bar).

Recording an Excel Macro

Before launching your recording in Excel, make sure the program is set up as required. For example, open the workbook and select the worksheet you want to use during the recording. Here are the steps to follow to record a macro in Excel:

1. Choose View, pull down the Macros menu, and then choose Record Macro. (You can also click the Macro Recording button in the status bar.) The Record Macro dialog box appears. Figure 1.3 shows the Excel version.

Figure 1.3

Use Excel's Record Macro dialog box to name and describe your macro.

Record Ma cm PFll^3j

Macro name:

| NewWorkbookTasks |

ShQrtmt key:

| Personal Macro Workbook |

Description:

For new workbooks sets Narrow marginsr turns offgridlines, inserts the company logor and display the Document Properties information pane.

2. Use the Macro Name text box to change the name to something memorable or descriptive. (Follow the same naming conventions as I outlined in the previous section.)

3. In Excel, you can use the Shortcut Key: Ctrl+ text box to assign a shortcut key to the macro. Note, however, that this is optional because VBA offers other ways to run your recorded macros (see "Running a Recorded Macro," later in this chapter).

4. Use the Store Macro In drop-down list to specify where the macro will reside. 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.

Excel's Personal Macro Workbook doesn't exist until you assign at least one recorded macro to it. After you do that,the Personal Macro Workbook (its filename is PERSONAL.XLSB) opens automatically every time 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. Note, however, that you don't see the Personal Macro Workbook when you start Excel because the file is hidden. If you want to see this workbook,you have to first unhide it: Choose the View, Unhide command,select Personal in the Unhide dialog box,and then click OK.

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

6. Click OK. Excel returns you to the workbook and starts recording.

7. Perform the tasks you want to include in the macro. Here are some things to bear in mind during the recording:

• Excel gives you just one indication that a recording is in progress: The status bar's Record Macro button changes to a blue square (see Figure 1.2, earlier).

• Unlike Word, Excel makes the mouse available for all actions.

• Because the macro recorder takes note of everything you do, be careful not to perform any extraneous keyboard actions or mouse clicks during the recording.

8. When you finish the tasks, choose View, pull down the Macros menu, and then choose Stop Recording (or click the Macro Recording button in status bar).

0 0

Post a comment