Microsoft Office Tutorials and References
In Depth Information
Chapter 9: Working with VBA Sub Procedures
within the procedure generally perform logical operations on these arguments, and the results of
the procedure are usually based on those arguments.
Although this chapter focuses on Sub procedures, VBA also supports Function
procedures, which I discuss in Chapter 10. Chapter 11 has many additional examples of
procedures, both Sub and Function , that you can incorporate into your work.
Declaring a Sub procedure
A procedure declared with the Sub keyword must adhere to the following syntax:
[Private | Public][Static] Sub name ([arglist])
Here’s a description of the elements that make up a Sub procedure:
h Private : (Optional) Indicates that the procedure is accessible only to other procedures
in the same module.
h Public : (Optional) Indicates that the procedure is accessible to all other procedures in
all other modules in the workbook. If used in a module that contains an Option
Private Module statement, the procedure is not available outside the project.
h Static : (Optional) Indicates that the procedure’s variables are preserved when the
h Sub : (Required) The keyword that indicates the beginning of a procedure.
h name : (Required) Any valid procedure name.
h arglist : (Optional) Represents a list of variables, enclosed in parentheses, that receive
arguments passed to the procedure. Use a comma to separate arguments. If the
procedure uses no arguments, a set of empty parentheses is required.
h instructions : (Optional) Represents valid VBA instructions.
h Exit Sub : (Optional) A statement that forces an immediate exit from the procedure
prior to its formal completion.
h End Sub : (Required) Indicates the end of the procedure.
With a few exceptions, all VBA instructions in a module must be contained within
procedures. Exceptions include module-level variable declarations, user-defined data type
definitions, and a few other instructions that specify module-level options (for example,
Option Explicit ).