Page 119 - Excel Progamming Weekend Crash Course

h540629 ch07.qxd 9/2/03 9:33 AM Page 94

94 Saturday Morning

Variables in Procedures

Most of the variables that a program uses are declared within procedures. Such variables are
called local variables because their scope is local to the procedure in which they are
declared. In other words, a local variable does not exist outside the procedure.

See Session 4 for information on declaring VBA variables.

Cross-Ref

Do not try to define a user-defined type or an enumeration within a proce-
dure — it is not permitted. You can, however, declare variables of a UDT or
Never Enum type that is defined elsewhere.
Each time a procedure is called, its local variables are created and initialized. This means
that a local variable does not retain its value between calls to the procedure. Usually this is
fine, but there may be situations where you want a local variable to “remember” its value.
This is done using the Static keyword. You can make a single variable static by using the
keyword in the variable declaration:
Static X As Integer

Alternatively, you can make all variables static by using the keyword in the procedure
definition:

Static Sub SubName()
...
End Sub
The code in Listing 7-1 demonstrates this.

Listing 7-1 Demonstrating static variables

Public Sub TestStatic1()
Dim i As Integer
For i = 1 To 5
TestStatic2
Next
End Sub

Public Sub TestStatic2()
Static A As Integer
Dim B As Integer
Debug Print A, B
A = A + 1
B = B + 1
End Sub