Microsoft Office Tutorials and References
In Depth Information
Executing Sub Procedures
The following example declares a private procedure named MySub :
Private Sub MySub()
‘ ... [code goes here] ...
End Sub
You can force all procedures in a module to be private — even those declared with the
Public keyword — by including the following statement before your first Sub statement:
Option Private Module
If you write this statement in a module, you can omit the Private keyword from your
Sub declarations.
Excel’s macro recorder normally creates new Sub procedures called Macro1 , Macro2 , and so
on. Unless you modify the recorded code, these procedures are all public procedures, and they
will never use any arguments.
Executing Sub Procedures
In this section, I describe the various ways to execute, or call, a VBA Sub procedure:
h With the Run Run Sub/UserForm command (in the VBE menu). Or you can press the F5
shortcut key, or click the Run Sub/UserForm button on the Standard toolbar.
h From Excel’s Macro dialog box.
h By using the Ctrl key shortcut assigned to the procedure (assuming that you assigned
one).
h By clicking a button or a shape on a worksheet. The button or shape must have the
procedure assigned to it.
h From another procedure that you write. Sub and Function procedures can execute
other procedures.
h From a custom control in the Ribbon. In addition, built-in Ribbon controls can be
“repurposed” to execute a macro.
h From a customized shortcut menu.
h When an event occurs. These events include opening the workbook, saving the
workbook, closing the workbook, changing a cell’s value, activating a sheet, and many other
things.
h From the Immediate window in the VBE. Just type the name of the procedure, including
any arguments that may apply, and press Enter.
 
Search JabSto ::




Custom Search