VBA Programming Series...12

Executing a macro by using a shortcut key

Another way to execute a macro is to press its shortcut key. But before you can use this method, you must assign a shortcut key to the macro.

You have the opportunity to assign a shortcut key in the Record Macro dialog box when you begin recording a macro. If you create the procedure without using the macro recorder, you can assign a shortcut key (or change an existing shortcut key) by using the following procedure:

1. Choose Developer➪Code➪Macros.

2. Select the Sub procedure name from the list box. In this example, the procedure is named CubeRoot.

3. Click the Options button.

Excel displays the dialog box shown in Figure 5-4.

4. Click the Shortcut Key option and enter a letter in the box labeled Ctrl.

The letter you enter corresponds to the key combination you want to use for executing the macro. For example, if you enter the lowercase letter c, you can then execute the macro by pressing Ctrl+C. If you enter an uppercase letter, you need to add the Shift key to the key combina-tion. For example, if you enter C, you can execute the macro by pressing Ctrl+Shift+C.

5. Click OK or Cancel to close the Macro Options dialog box.

After you’ve assigned a shortcut key, you can press that key combination to execute the macro. The shortcut keys you assign to macros override Excel’s built-in shortcut keys. For example, if you assign Ctrl+C to a macro, you can’t use this shortcut key to copy data in your workbook. This is usually not a big deal because Excel always provides other ways to execute commands.