Page 57 - Microsoft Office Excel 2003 Programming Inside Out

Exploring Visual Basic for Applications

For more information on creating relative references using the Offset property, see “Referring to
Ranges” in Chapter 8: “Ranges and Cells".

Running a Macro

Once you’ve created a macro, you can run it at any time by clicking Tools, Macro, Macros,
<macro_name>, Run. Also, in the Macro dialog box, you can display the macros available in
other workbooks by clicking the arrow next to the Macros In box and selecting a workbook
by name or by selecting All Open Workbooks, which will display every macro in any open
workbook. If you select either of those choices, the macro names displayed will include the
name of the workbook in which the macro is stored.

Tip Running Macros Automatically
If you want to have a macro run every time a particular workbook is opened, name the
macro Auto_Open.

There are several other ways you can run a macro in Excel, though they require you to assign
the macro to an action a user can take. Those actions are: Chapter 3
● Pressing a key sequence.
● Linking a macro to a toolbar button.
● Linking a macro to a menu item.

Assigning a Macro to a Key Sequence
For readers who prefer to use keyboard shortcuts such as Ctrl+C (for copying) when you use
Excel, you can assign macros to key sequences. It’s important to remember that if you assign
a macro to a key sequence that’s already taken (for example, Ctrl+S, which saves a workbook),
the new assignment will take precedence over the default behavior in the workbook where
the assignment was made.
To assign a key sequence to a macro, follow these steps:

1 Click Tools, Macro, Macros.
2 In the Macro name box, select the name of the macro to which you want to assign to a
keyboard shortcut key.
3 Click Options.
4 Type a letter in the Shortcut key box. Typing a lowercase letter lets you use Ctrl+letter,
while typing an uppercase letter lets you use Ctrl+Shift+letter as the shortcut.

Note The shortcut key must be a letter; you can’t pick a number or special character
such as the dollar sign ($) or ampersand (&).

31
Part 2: Visual Basic for Applications