Executing Sub Procedures

Although you may not know much about developing Sub procedures at this point, I'm going to jump ahead a bit and discuss how to execute these procedures. This is important because a Sub procedure is worthless unless you know how to execute it.

By the way, executing a Sub procedure means the same thing as running or calling a Sub procedure. You can use whatever terminology you like.

You can execute a VBA Sub in many ways — that's one reason you can do so many useful things with Sub procedures. Here's an exhaustive list of the ways (well, at least all the ways I could think of) to execute a Sub procedure:

1 With the RunORun Sub/UserForm command (in the VBE). Excel executes the Sub procedure at the cursor position. This menu command has two alternatives: The F5 key, and the Run Sub/UserForm button on the Standard toolbar in the VBE. These methods don't work if the procedure requires one or more arguments.

1 From Excel's Macro dialog box (which you open by choosing ToolsO MacroOMacros). Or you can press the Alt+F8 shortcut key. When the Macro dialog box appears, select the Sub procedure you want and click Run. This dialog box lists only the procedures that don't require an argument.

1 Using the Ctrl+key shortcut assigned to the Sub procedure (assuming you assigned one).

1 Clicking a button or a shape on a worksheet. The button or shape must have a Sub procedure assigned to it.

1 From another Sub procedure that you write.

1 From a Toolbar button. (See Chapter 19.)

1 From a custom menu you develop. (See Chapter 20.)

1 Automatically, when you open or close a workbook. (See Chapter 11.)

1 When an event occurs. As I explain in Chapter 11, these events include saving the workbook, making a change to a cell, activating a sheet, and other things.

1 From the Immediate window in the VBE. Just type the name of the Sub procedure and press Enter.

I demonstrate some of these techniques in the following sections. Before I can do that, you need to enter a Sub procedure into a VBA module.

1. Start with a new workbook.

2. Press Alt+F11 to activate the VBE.

3. Select the workbook in the Project window.

4. Choose InsertOModule to insert a new module.

5. Enter the following into the module:

Sub CubeRoot()

Num = InputBox("Enter a positive number") MsgBox Num A (1 /3) & " is the cube root."

End Sub

This simple procedure asks the user for a number and then displays that number's cube root in a message box. Figures 5-1 and 5-2 show what happens when you execute this procedure.

Figure 5-1:

Using the built-in VBA InputBox function to get a number.

Figure 5-1:

Using the built-in VBA InputBox function to get a number.

Figure 5-2:

Displaying the cube root of a number via the MsgBox function.

Microsoft Excel


n 113561 is the cube root.


By the way, CubeRoot is not an example of a good macro. It doesn't check for errors, so it fails easily. To see what I mean, try clicking the Cancel button in the input box or entering a negative number.

0 0

Post a comment