Using the Macro Recorder

The Macro Recorder lets you record keystrokes and actions that you perform as a VBA program. You can use it to record complete tasks, such as setting up a document, or for partial tasks, such as highlighting text and giving it certain attributes. The Macro Recorder can help you perform the following tasks:

1 Create a macro based on your actions. 1 Discover how Word performs certain tasks. i Decide how to break your program into tasks. i Help you create the basis for a more complex program.

The Macro Recorder isn't a complete solution for your VBA needs. For example, you can't use the Macro Recorder to create interactive programs without extra coding. The same holds true for programs that must change based on user input, the environment, or the data you're manipulating. All of these tasks require you to add more code. However, it's a good starting point for many structured programming tasks. You can get the basics down quickly using the Macro Recorder and then make changes as needed. The macro recording process follows the same basic steps no matter which version of Office you use:

1. Start the Macro Recorder.

2. Perform all of the steps that you normally perform to accomplish a task.

3. Stop the Macro Recorder.

4. Save the macro when the Office application prompts you.

5. Optionally, open the resulting macro and make any required changes.

Recording a macro using the Ribbon interface

Recording a macro with the new features provided by the Office 2007 Ribbon interface is easier than in past versions. Microsoft has added features that reduce the complexity of creating a macro. For example, when you press Alt, you see the number or letter you must press to perform a particular action in little boxes over each control on the Ribbon.

If you're used to using the mouse to perform most Office tasks, you may want to practice the keystrokes you need to use to record the macro several times. Recording the macro without mistakes makes it run faster and also makes it easier to edit the recorded macro later. The following steps describe how to record a macro using the Ribbon interface:

1. Select the Developer tab.

Because Office 2007 doesn't display this tab by default, see the "Batteries Included — VBA Comes with Office" section of Chapter 1 for details on displaying it.

You see the Developer tab, shown in Figure 3-1.

Figure 3-1:

The Developer tab contains most of the items you need to work with macros.

Figure 3-1:

The Developer tab contains most of the items you need to work with macros.

2. Click Record Macro.

The Office application displays the Record Macro dialog box, shown in Figure 3-2.

Figure 3-2:

Use the Record Macro dialog box to type details about your macro.

Figure 3-2:

Use the Record Macro dialog box to type details about your macro.

3. Type a descriptive name for the macro.

4. Type a control-key combination for the macro when you want to access it from the keyboard.

Use this option only for major macros because you don't want to use up all of the available key combinations.

5. Select a storage location in the Store Macro In field.

The storage locations vary by Office application. Here's an explanation of the locations for Excel:

• This Workbook: Use this option when you want to store the macro within the local file. Anyone opening the file can access the macro.

• Personal Macro Workbook: Use this option when you want to store the macro in a special workbook that contains all of your personal macros. This storage location makes the macro available to you at all times. It doesn't matter which workbook you open.

• New Workbook: Use this option when you want to store the macro in a new workbook.

The storage locations for Word are similar, as explained in the following list:

• Document: Use this option to store the macro within the local file. Anyone opening the file can access the macro.

• Document Template: Use this option to store the macro within the template used with the document. Anyone who creates a document that relies on the template can access the macro.

• All Documents (Normal.dotm): Use this option to store the macro within the global template. Storing the macro here means that anyone opening a document of any kind can access the macro.

6. Type a macro description in the Description field.

It's essential to type a complete description because this comment is the only one the macro will contain when you complete it.

The Office application begins recording the macro. Notice that the Record Macro button changes to a Stop Recording button and that the button icon is now blue instead of red.

8. Perform any tasks that you would normally perform to complete the task.

The Office application records all of your keystrokes. However, it doesn't record mouse movements. Consequently, you should avoid using the mouse and perform all tasks using the keyboard.

9. Click Stop Recording.

The Office application finishes the macro.

You can view your new macro by clicking Macros on the Developer tab. The Macro dialog box shows the macros associated with the current document whether they appear locally or as part of an external document or template. The "Modifying the macro" section, later in this chapter, describes this dialog box in more detail.

Recording a macro using the menu interface

Older versions of Office and some Office 2007 products require that you use the menu interface to activate the Macro Recorder. The following steps describe how to record a macro using the menu interface:

1. Choose ToolsOMacroORecord New Macro.

The Office application displays a Record Macro dialog box similar to the one shown in Figure 3-2.

2. Type a descriptive name for the macro.

3. Type a control-key combination for the macro when you want to access it from the keyboard.

Use this option only for major macros because you don't want to use up all of the available key combinations.

Some older Office products include other options. For example, when working with an older version of Word, you can choose to associate the macro with either the keyboard or the toolbar, or both, by clicking the appropriate button and making the assignment.

4. Select a storage location in the Store Macro In field.

(This is an Office 2007 feature; older versions always store the macro in the local document.)

The storage locations vary by Office application. Here's an explanation of the locations for Visio:

• Active Document: Use this option when you want to store the macro within the local file. Anyone opening the file can access the macro.

• Stencil: Stores the file within the stencil file. Anyone using the stencil can access the macro no matter which document is opened.

5. Type a macro description in the Description field.

It's essential to type a complete description because this comment is the only one the macro will contain when you complete it.

The Office application begins recording the macro. You see the Stop Recording toolbar, shown in Figure 3-3. This toolbar includes options for stopping and pausing the macro recording. Pausing the recording lets you make changes that aren't required for the macro. Click Pause Recording a second time to start recording again.

7. Perform any tasks that you would normally perform to complete the task.

The Office application records all of your keystrokes. However, it doesn't record mouse movements. Consequently, you should avoid using the mouse and perform all tasks using the keyboard.

Figure 3-3:

Stop or Stop Recording pause the I_

macro [ 1 | j recording process as pauSe Recording necessary.

8. Click Stop Recording.

The Office application finishes the macro. The Stop Recording toolbar disappears.

You can see your new macro by choosing ToolsOMacroOMacros. The Macro dialog box contains a list of the macros associated with the current document, whether they're local or part of another document, stencil, template, or other associated file.

Although this book proper doesn't contain much material on Visio or FrontPage, you'll find a Visio Bonus Chapter on the Dummies.com Web site. Visit www.dummies.com/go/vbafd5e to download both the source code and the Bonus Chapters (one for Visio and another for FrontPage).

Modifying the macro

Modifying a macro recorded using the Macro Recorder is much like modifying any other macro. The only differences are that you didn't write the initial code and the Macro Recorder doesn't add any comments for you. As an example, open the Excel macro created earlier in this chapter, in the "Recording a macro using the Ribbon interface" section. The following steps describe how to perform this task:

1. Open the Macro dialog box. When using the Ribbon interface, you click Macros on the Developer tab. When using the menu interface, choose ToolsOMacroOMacros.

You see the Macro dialog box, shown in Figure 3-4.

2. Choose the macro you want to edit and then click Edit.

The Office application opens the Visual Basic Editor with the selected macro opened, as shown in Figure 3-5. (You may see other macros files opened as well.)

3. Add comments to the recorded macro so that you can retrace your steps later.

Figure 3-4:

The Macro dialog box contains a list of available

Figure 3-4:

The Macro dialog box contains a list of available

4. Make any required macro changes.

5. Save the macro and close the Visual Basic Editor.

Figure 3-5:

You use the Visual Basic Editor to modify any macros you create using the Macro Recorder.

Figure 3-5:

You use the Visual Basic Editor to modify any macros you create using the Macro Recorder.

It isn't necessary now to understand the macro shown in Figure 3-5. However, this macro begins by assigning a value of 1 to the worksheet cell at A1. Because the cursor was already in cell A1 when the macro recording started, this action doesn't appear in the macro. This omission points out one of the reasons you want to edit macros you create with the Macro Recorder. The macro then moves the cursor to cell B2 and assigns it a value of 2. Finally, the macro moves the cursor to cell C3 and enters an equation in it that sums the two numbers. You'd probably edit this macro by adding the missing cell reference for A1, adding comments, and removing the one extra statement, as shown in Figure 3-6.

Figure 3-6:

Editing Macro Recorder output is important when you want to use the macro for multiple tasks.

Excel Macro Recorder Example.xls - ExcelMacroRecorder (Code)

(General} ▼ MyTestMacro

Suh HyTestHacro ( )

HyTestHacro Macro

This is a test macro to show how the macro recorder wor]C3.

Suh HyTestHacro ( )

HyTestHacro Macro

This is a test macro to show how the macro recorder wor]C3.

■ Select tile first cell and enter information into it. Range("Al").Select

ActiveCell.FormnlaKlCl - "1"

■ Select the second cell and enter information into it. Range("El").Select

ActiveCell.FormalaRlCl - "2"

■ Create an equation for the third cell that sums the

ActiveCell.FormalaRlCl = "=SUH[RCE-2(:RCf-11}"

■ The macro recorder added chis encry, but you really

End Sub

Was this article helpful?

0 0

Post a comment