Databases Reference
In-Depth Information
X = "Yes"
MsgBox "The answer is " &X
End Sub
Static:
A local variable declared with the Static statement remains in existence the entire time Visual Basic
is running. The variable is reset when any of the following occur:
The macro generates an untrapped run-time error.
Visual Basic is halted.
Yo u quit Microsoft Excel.
Yo u change the module.
For example, in the RunningTotal example, the Accumulate variable retains its value every time
it is executed. The first time the module is run, if you enter the number
, the message box
will display the value “2.” The next time the module is run, if the value 3 is entered, the message
box will display the running total value to be 5.
2
Sub RunningTotal()
Static Accumulate
' Local variable that will retain its value after the module
' has finished executing.
num = Application.InputBox(prompt:="Enter a number: ",
Type:=1)
Accumulate = Accumulate + num
MsgBox "The running total is " & Accumulate
End Sub
F.2.2
M
S
ODULE
COPE
A variable that is recognized among all of the procedures on a module sheet is called a “module-level”
variable. A module-level variable is available to all of the procedures in that module, but it is not available
to procedures in other modules. A module-level variable remains in existence while Visual Basic is
running until the module in which it is declared is edited. Module-level variables can be declared with
a Dim or Private statement at the top of the module above the first procedure definition.
At the module level, there is no difference between Dim and Private. Note that module-level
variables cannot be declared within a procedure.
NOTE: If you use Private instead of Dim for module-level variables, your code may be easier to
read (that is, if you use Dim for local variables only, and Private for module-level variables, the
scope of a particular variable will be more clear).
In the following example, two variables, A and B, are declared at the module level. These two
variables are available to any of the procedures on the module sheet. The third variable, C, which
is declared in the Example3 macro, is a local variable and is only available to that procedure.
Note that in Example4, when the macro tries to use the variable C, the message box is empty. The
message box is empty because C is a local variable and is not available to Example4, whereas
variables A and B are.
Dim A As Integer
' Module-level variable.
Private B As Integer' Module-level variable.
