Chapter 11: Mastering VBA Data Types and Procedures
435
between calls to the procedure. Once a value has been assigned to a static variable, the variable
retains its value until it’s changed in another call to the procedure.
An alternative to using static variables is to declare a global or module-level variable and use it each
time a particular procedure is called. The problem with this approach is that a global or module-
level variable is accessible to other procedures that are also able to modify its value. You can expe-
rience undesirable side-effect bugs by unwittingly changing the value of a widely-scoped variable
without realizing what has happened. Because of their procedure-limited scope, static variables are
one way to avoid side-effect bugs.
Incidentally, declaring a procedure with the Static keyword makes all variables in the procedure
static as well. In the following listing, both variables — intStatic and intLocal — in the
StaticTest2 sub are static, in spite of their local declarations within the procedure. The
Static keyword used in the procedure’s heading makes both variables static in nature.
Private Static Sub StaticTest2()
Dim intStatic As Integer
Dim intLocal As Integer
intStatic = intStatic + 1
intLocal = intLocal + 1
txtStatic = intStatic
txtLocal = intLocal
End SubUnderstanding Subs and Functions
The code in a VBA application lives in containers called modules. As you learned in Chapter 10,
modules exist behind the forms in an Access application as well as in standalone modules. The
modules themselves contain many procedures, variable and constant declarations, and other direc-
tives to the VBA engine.
The code within the modules is composed of procedures. There are two main types of procedures
in VBA: subroutines or subprocedures (often called subs) and functions.
The general rules for procedures include the following:
l You must give the procedure a unique name within its scope (see “Understanding
variable scope and lifetime,” earlier in this chapter). Although it isn’t a good idea —
because of the chance of confusing the VBA engine or another person working with your
code — it is possible to have more than one procedure with the same name, as long as the
name is unique within each procedure’s scope.l (^) The name you assign to a procedure can’t be the same as a VBA keyword or the
name of a built-in VBA procedure.
l (^) A procedure and a module cannot have the same name. This is one place where a nam-
ing convention can be very useful. If you always prefix module names with bas or mod,