VBA Static Variable

A VBA Static variable retains its value even when the procedure ends.

Declaring a Static Variable

You declare a Static variable using the Static keyword as shown below:

Sub DeclaringAStaticVariable()

Static myVariable As Integer
MsgBox myVariable
myVariable = myVariable + 2

Range("A1").Value = myVariable

End Sub

The first time you press F5, you get a Message box showing the initial value of myVariable which is 0. The value of 2 is placed in cell A1.

Message Box showing Initial Value of Variable

The Second Variable Value

If you press F5 again immediately afterwards, you get the following Message box showing the value of myVariable which is 2, and the value of 4, placed in your worksheet in cell A1.

Second Variable Value in Worksheet

As you can see the variable is retaining its value even as the Sub Procedure ends, if you used the Dim keyword instead of the Static keyword to declare the variable, the Message Box would always show the value of 0. Also, the result in cell A1, would be 2, every time you clicked F5. Since the variable would not retain its value once the Sub Procedure ended.