Executing Sub Procedures

In this section, I describe the many ways to execute, or call, a VBA Sub procedure:

♦ With the Run Sub/UserForm command (in the Visual Basic Editor; VBE). Or you can press the F5 shortcut key. Excel executes the procedure at the cursor position. This method doesn't work if the procedure requires one or more arguments.

♦ From Excel's Macro dialog box, which you open by choosing Tools ^ Macro ^ Macros. Or you can press the Alt+F8 shortcut key to access the Macro dialog box.

♦ By using the Ctrl key shortcut assigned to the procedure (assuming that you assigned one).

♦ By clicking a button or a shape on a worksheet. The button or shape must have the procedure assigned to it.

♦ From another procedure that you write. Sub and Function procedures can execute (or "call") other procedures.

♦ From a custom menu that you develop.

♦ When an event occurs. These events include opening the workbook, saving the workbook, closing the workbook, making a change to a cell, activating a sheet, and many other things.

♦ From the Immediate window in the VBE. Just type the name of the procedure, including any arguments that may apply, and press Enter.

I discuss these methods of executing procedures in the following sections.

In many cases, a procedure will not work properly unless it is in the appropriate context. For example, if a procedure is designed to work with the active worksheet, it will fail if a chart sheet is active. A good procedure incorporates code that checks for the appropriate context and exits gracefully if it can't proceed.

0 0

Post a comment