Microsoft Office Tutorials and References
In Depth Information
Chapter 3: Writing Smarter Code
✦ Public farReaching As String: The variable named farReaching
is visible to all procedures in all modules because it’s declared by using
the Public keyword.
✦ Private notSoFarReaching As String and Dim alsoNotSoFar
Reaching As String: These variables are visible to all procedures
in the same module but aren’t visible to procedures defined in other
modules. Dim and Private have the same meaning in this context.
✦ Dim existsOnlyHere As String: Because this variable is declared
within a procedure, the variable is visible only to that procedure.
The lifetime of a variable defines how long a variable retains a value. When you
open a database, variables defined at the module level of standard modules
are created and can be assigned a new value at any time. The lifetime of
such variables is lengthy. These variables exist and can contain values for
the entire session, from when you open the database to when you close it.
Variables declared with a Dim keyword at the procedure level have a much
shorter lifetime. The variable retains its value for only as long as the
procedure runs. A second call to the same procedure re-creates the variables and
assigns new values to them.
Although it’s rare to do so, sometimes you may want to make one or more
variables retain their values between calls to the procedure. You may have
a variable that keeps track of how many times the procedure is called, for
example. In that case, you can use the Static keyword, rather than Dim, to
declare the variable or variables. The following statement defines a static
variable named howMany, which stores an integer (whole number):
Static howMany as Integer
You can make all variables declared within a procedure static by preceding
the Sub or Function keyword with Static. All variables defined within the
following procedure are static because the Static keyword in front of the
word Function makes all that procedure’s variables static:
Static Function myFunction()
‘Both variables below are static
Dim var1 As String
Dim var2 As Byte
If you find variable scope and lifetime terribly confusing, you rarely need to
be so picky about the scope and lifetimes of variables. In fact, if you never
use the Public, Private, or Static keywords in any code you write,
chances are that the code will still work perfectly; the default scope and
lifetime assigned to a variable through the Dim statement usually are exactly
what you need. Exceptions are few and far between, and they aren’t likely to
show up until you start developing huge, complex databases.