How to Execute a VBA Sub Procedure in Excel 2016 - dummies

How to Execute a VBA Sub Procedure in Excel 2016

By John Walkenbach

You need to know how to execute VBA Sub procedures in Excel 2016. 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 to execute a Sub procedure:

  • With the Run → Run Sub/UserForm command (in the VBE). Excel executes the Sub procedure in which the cursor is located. 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.

  • From Excel’s Macro dialog box. You open this box by choosing Developer → Code → Macros or by choosing View → Macros → Macros. Or bypass the Ribbon and just 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.

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

  • Clicking a button or a shape on a worksheet. The button or shape must have a Sub procedure assigned to it — which is very easy to do.

  • From another Sub procedure that you write.

  • From a button that you’ve added to the Quick Access toolbar.

  • From a custom item you’ve added to the Ribbon.

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

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

Now, you need to enter a Sub procedure in 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 Insert → Module to insert a new module.

    Enter the following in the module:

Sub ShowCubeRoot()
  Num = InputBox(“Enter a positive number”)
  MsgBox Num ^ (1/3) & “ is the cube root.”
End Sub

This procedure asks the user for a number and then displays that number’s cube root in a message box. This is what happens when you execute this procedure.

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

By the way, ShowCubeRoot is not an example of a good macro. It doesn’t check for errors, so it fails easily.

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

To see why it isn’t a good macro, try clicking the Cancel button in the input box or entering a negative number. Either action results in an error message.