Running a Recorded Macro

In almost all cases, you record a macro so that you can run it again in the future, probably a number of times depending on the tasks you recorded. (The exception would be, as I mentioned earlier, if you record a macro to use as a starting point for writing your own code.) So after you record a macro, how do you get it to run again? There are three main methods you can use: the Macro Name list, a shortcut key, and a Quick Access toolbar button.

Using the Macro Name List

The Macro Name list is a listing of all your recorded macros. (It also contains macros you create by hand, as discussed in the next chapter.) This means that all your recorded macros are as little as four mouse clicks away, as you see in the following steps:

1. Set up the document so that it's ready to handle the tasks that the recorded macro will run (for example, open a document, move the cursor into position, or select text).

2. Choose View, Macros (or press Alt+F8). Word displays the Macros dialog box (see Figure 1.4), although in Excel it's called the Macro dialog box.

3. (Optional) Use the Macros In list to click the template or document that contains the macro.

4. In the Macro Name list, click the macro you want to run.

5. Click Run. The program runs the macro.

Figure 1.4

Use the Macro Name list to select the macro you want to run.

Macros [ V ll^^ll

Macro name:

AdjustWindowSize

Run

FixParagraphlndents NewDocumentSteps ReplaceFcrmatting

Edit Create Delete

Organizer...

Macros in; | Normal.dotm (global template)

H

Description:

Adjusts the size and position of the current window.

Cancel

Assigning Shortcut Keys to Recorded Word Macros

If you have a recorded macro that you'll be using frequently, even the few mouse clicks required to run the macro from the Macro Name list can seem excessive. A faster alternative is to assign a shortcut key to the macro, which means you can run the macro by pressing the shortcut key.

To assign a shortcut key in Word, follow these steps:

1. You have two ways to get started:

• If you haven't recorded the macro yet, choose View, pull down the Macros list, and then choose Record Macro. Fill in the macro details (name, storage location, and description) first, and then click Keyboard. Skip to step 4.

• If you've already recorded the macro, choose Office, Word Options, click Customize, and then click the Customize button beside the Keyboard Shortcuts text.

2. In the Customize Keyboard dialog box, use the Categories list to click Macros. Word displays your macros in the Macros list.

3. In the Macros list, click the macro you want to work with.

4. Click inside the Press New Shortcut Key box and then press the shortcut key you want to use. One of two things will happen:

• Word displays Currently Assigned To, followed by [unassigned], as shown in Figure 1.5. This means no other command is using the shortcut key, so proceed to step 5.

Figure 1.5

Use Word's Customize Keyboard dialog box to assign a shortcut key to a macro.

Customize Keyboard Speafy a command Categories:

Customize Keyboard Speafy a command Categories:

Print Preview Tab *

f.f! iiHîïiT^TPT^H

Outlining Tab

RxParagraphlndents

All Commands

NewDocumentSteps

ReplaceFormatbng

Fonts

AutoText

Styles

Specify keyboard sequence Current keys:

Press new shortcut key: Alt+Ctrl+W

Specify keyboard sequence Current keys:

Press new shortcut key: Alt+Ctrl+W

Currently assigned to: [unassigned] Save changes in: | Normal Description

Adjusts the size and position of the current window, | Assign |j | Remove | [ Reset All. ■■ |

• Word displays Currently Assigned To, followed by the name of a command. This means that another Word command (or macro) is already using the shortcut key. Repeat step 4 until you find an unassigned shortcut key.

CAUTION _

It's best to avoid overwriting any of Word's built-in shortcuts because you may use them now or in the future. By using key combinations that include some or all of the Shift, Ctrl, and Alt keys, you can almost always find an unassigned shortcut for your macros.

^ If you have trouble remembering your keyboard shortcuts,you can get Word to print out a list of them. Choose Office,Print to open the Print dialog box. In the Print What list,click Key Assignments, and then click OK.

5. Click Assign.

6. Click Close.

7. If you opening the Word Options dialog box earlier, click OK.

Assigning Shortcut Keys to Recorded Excel Macros

If you want to assign a shortcut key to a recorded Excel macro, you have two ways to get started:

■ If you haven't recorded the macro yet, choose View, pull down the Macros list, and then choose Record Macro. Fill in the macro details (name, storage location, and description) first, and then click Keyboard. Skip to step 4.

■ If you've already recorded the macro, choose View, Macros (or press Alt+F8) to display the Macro dialog box. Click the macro you want to work with and then click Options to display the Macro Options dialog box shown in Figure 1.6.

Figure 1.6

Use the Macro Options dialog box to assign a shortcut key to a macro.

Macrrj Options

MaciD name:

PERSONAL. KLSBiNewWorkbookTasks

ShQrtmt

Description:

FDr new workbooks, sets Narrow margins, turns Dffgridlines, riserts the company logor and display the Document Properties information pane.

mi^i

In the Shortcut Key Ctrl+ text box, type the letter you want to use with Ctrl for the key combination. For example, if you type e, you can run the macro by pressing Ctrl+E. Click OK.

¡¡j Excel shortcut keys are case sensitive, meaning you can create separate shortcuts with uppercase ® and lowercase letters. For example, if you type e into the Ctrl+ text box,you have to press Ctrl+E (or, to be precise, Ctrl+e) to run the macro. However, if you type e into the Ctrl+ text box, you have to press Ctrl+Shift+E to run the macro.

CAUTION _

Make sure you don't specify a shortcut key that conflicts with Excel's built-in shortcuts (such as Ctrl+B for Bold or Ctrl+C for Copy). If you use a key that clashes with an Excel shortcut, Excel overrides its own shortcut and runs your macro instead (provided that the workbook containing the macro is open).

There are only four letters not assigned to Excel commands that you can use with your macros: e,j, m,and q. You can get extra shortcut keys by using uppercase letters. Note, however,that Excel uses four built-in Ctrl+Shift shortcuts:A,F,O,and P.

Creating a Quick Access Toolbar Button for a Recorded Macro

The only problem most people have with assigning shortcut keys to macros is remembering which shortcut runs which macro! The more shortcuts you assign, the harder it gets to remember them all and the more likely it is that you'll press an incorrect shortcut key by mistake. What many VBA veterans do is assign just a few shortcut keys to their most frequently used macros, and other macros that they need handy they assign to the Quick

Access toolbar. This is a great way to run oft-used macros because they're only a click away and you can assign different icons to each macro to help you differentiate them.

Follow these steps in either Word or Excel to create a Quick Access toolbar button for a macro:

1. Click the Customize Quick Access Toolbar button and then choose More Commands. The application's Options dialog box appears with the Customize tab displayed.

2. In the Choose Commands From list, click Macros. A list of your macros appears.

3. Click the macro you want to work with and then click Add. The program adds the macro to the list of Quick Access toolbar buttons.

4. To change the macro button's icon, click the macro in the list of Quick Access toolbar buttons and then click Modify. The Modify Button dialog box appears, as shown in Figure 1.7.

Figure 1.7

Use the Modify Button dialog box to assign an icon and display name to your macro button.

Modify Button I ff ll^l

Symbol:

a^u ja'Q^iaa^tauia aii-^s^«* * » * ® P1 a fl a ^[alQT'va! iJ1®^ « ^ fc a a a ■:*:■« ^ © i i ^ G "£" » □ □ □ □ □ □□□□□□HB6MUD

A| A X v-fc£0[!!] JC

J

□¡splay name: ¡Adjust Window Size

| Ot |j CantEl

5. Use the Symbol list to click the icon you want to use for the macro button.

6. Use the Display Name text box to type the name you want to appear when you hover the mouse pointer over the button.

8. Repeat steps 3-7 to assign other macros to buttons.

Figure 1.8 shows a macro button added to the Quick Access toolbar.

Figure 1.8

A macro button added to the Quick Access toolbar.

Macro button

Figure 1.8 shows a macro button added to the Quick Access toolbar.

Macro button

In its default position above the Ribbon, the Quick Access toolbar can display only so many buttons. If you want to add lots of buttons for your macros (or other program commands),move the Quick Access toolbar below the Ribbon. Click the Customize Quick Access Toolbar button and then choose Show Below the Ribbon.

From Here

■ You'll learn more about the Visual Basic Editor as well as how to create your 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."

This page intentionally left blank

0 0

Post a comment