Static Variables in VBA

Normally variables cease to exist once your Sub or Function has finished executing. Static variables allow you to preserve a value after your code has finished.

Let's say you want to keep track of the number of times something happens, like how many times someone clicks on this button they are not supposed to.

If you use a normal variable declaration with Dim

then the variable clicks ceases to exist when the Sub CountClicks ends.

Every time the big red button is pressed, clicks is recreated with an initial (uninitialized) value of 0. So clicks + 1 will be 1.

If you declare the variable as Static

Then clicks retains its value every time the Sub is run, with its value increasing each time the button is pressed.

Download The Sample Workbook

After opening the workbook, open the VBA editor (ALT+F11) and then in the Immediate Window watch the value of clicks being printed each time you click that big red button.

If the Immediate Window isn't visible, press CTRL+G or go to the View Menu and click on Immediate Window.

When Does a Static Variable Lose its Value?

If you are in the VBA editor and click on the Reset button, the static var will be destroyed. Next time the CountClicks sub is run, clicks will be 0 again.

When you close the workbook, the clicks var is also removed from memory so when you re-open the workbook, it will be uninitialized (its default value is 0).