Static Variables

Normally, when a procedure ends, all the variables are reset. Static variables are a special case because they retain their value even when the procedure ends. You declare a static variable at the procedure level. A static variable may be useful if you need to track the number of times you execute a procedure. You can declare a static variable and increment it each time you run the procedure.

As shown in the following example, you declare static variables by using the Static keyword:

Sub

MySub()

Static Counter As Integer

Dim Msg As String

Counter = Counter + 1

Msg = "Number of executions: " & Counter

MsgBox Msg

End

Sub

The code keeps track of the number of times the procedure was executed. The value of the Counter variable is not reset when the procedure ends. But it is reset when you close and reopen the workbook.

Even though the value of a variable declared as Static is retained after a variable ends, that variable is unavailable to other procedures. In the preceding MySub procedure example, the Counter variable and its value are available only within the MySub procedure. In other words, it's a procedure-level variable.

0 0

Post a comment