VBA Static Variable

Associated Files Download Links

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:

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.

You may also like some of this related content...

We covered arrays, static arrays and dynamic arrays in a previous tutorial.  We are going
We covered an introduction to variables and constants in our VBA Data Types – Variables and
We have already covered an introduction to variables, constants and VBA data types in an
We are going to cover Global Variables, in this tutorial. A variable in VBA can
We have already gone over what variables and constants are, in our VBA Data Types
Advertisements
Automate Excel
Left Menu Icon