When to Use Static Variables in VBA?

Static Variables in VBA are particular kinds of variables that are used in different situations. The usage of a Static Variable in VBA is irreplaceable. They are straightforward to implement.

So let us begin learning them.

What are Static Variables?

A static variable retains its value even after the Sub Procedure ends. We use a static variable in Sub Procedure when we need to call the Procedure multiple times and the variable has to retain its value from the last Sub Procedure call to the next Sub Procedure Call.

A non Static variable loses its value when the block containing the variable declaration ends. The Static Variables in VBA are not erased from the computer’s memory even after the Sub Procedure Call ends.

The Scope of the static variable is where we can access its value. The scope is limited to the Sub Procedure in which it is declared.

The lifetime of a static variable is Module level. It loses its value when we close the workbook, restart the PC in the middle when the workbook is opened, or reset the execution.

You can have the practice workbok from below.

Syntax to Declare Static Variable

Static <variable_name> As <data_type>

The following points explain the syntax to declare a static variable.

  • The static keyword is used to declare a Static Variable.
  • After that, we specify the name of the variable.
  • Followed by the As keyword, we tell the data type of the static variable.

Look at these declarations for static variables as an example.

Sub Example_Procedure()
Static Count as Integer
Static Point as Double
Static IsTrue as Boolean
End Sub
infographics static variables in vba

Example 1 – Print Multiples of 5 using a Static Variable

Let us suppose we are willing to print a series of multiples of 5. The next multiple of 5 in the series prints when we call the Sub Procedure. We would implement the use of static variables in VBA. Follow these steps now.

  • Hold the Alt key and press F11 to open VBE in Excel.
  • Go to the Insert tab and Insert a New Module.
  • Enter the following Sub Procedure in the New Module.
Sub Example1()
Static count As Integer
count = count + 5      'updates the value of static variable
Debug.Print count      'print the value of static variable
End Sub
  • Press Ctrl and G keys to open the Immediate Window. The value of the static variable count would be printed there.
  • Put the mouse cursor in the Example1 Sub Procedure and press the F5 key multiple times with your eyes on Immediate Window.
how to use a static variable in vba

Explanation – The following points would explain the result.

  • When we press the F5 key for the first time, the Sub Procedure runs. Initially, the value of the static variable count is 0. We use the statement count=couunt+5 to increment and print the count in Immediate Window using Debug.Print count.
  • The count variable contains 5. Now we press the F5 key a second time. count variable value is 5 and now it would be incremented by 5 and the result would be 10. We print the value of the count using Debug.Print statement.
  • Every time we press the F5 key, the value of the count variable from the previous Sub Procedure call will be retained, incremented by 5, and then printed onto the Immediate Window. The series of multiples would keep on expanding as you Re Run the Sub Procedure.

Declaring Static Sub Procedure

We can also use the Static keyword with the Sub Procedure declaration to make it static. This will make all the local variables of the Sub Procedure to be Static.

Storage space for all the variables of the Sub Procedure would be allocated at once in the computer memory and will be retained until we close the workbook.

Static Sub <Sub_procedure_name>()
Dim <variable_name> As <data_type>                           'this would become static variable
End Sub

Example 2 – Count the Number of Clicks on the Command Button

We would count the total number of clicks on a Command Button. We will make all the local variables of the Click Event to be static using the Static keyword. Follow the steps below.

  • Go to the Developer tab. Click on the Insert button in the Controls Group. Choose a Command Button in Form controls.
  • Drag your mouse into the cell area to insert a command button named Button1.
  • When you release the mouse, the Assign Macro dialog box would appear. Click on the New button at the right.
  • This opens the VBA module having a Sub Procedure which would be executed when the Button1 is clicked.
static sub procedures in vba

Use the following code in Module2.

Static Sub Button1_Click()
Dim count_click As Integer
count_click = count_click + 1
MsgBox "Button Clicked " & count_click & " times"
End Sub
  • Paste this code and close the VBE. Now when you click the button, a message box would be displayed showing the number of times you clicked on the Button1. Every time you click Button1, Button1_Click in module2 would be executed.
count the number of times button is clicked

How to Reset the Value of Static Variables?

When we Re Run a Sub Procedure multiple times, the value of the static variable updates with each call. We can reset the value of the static variable to zero.

With this method, we would reset the value of all the static variables of all the Sub Procedures in all our modules. To reset the value of the static variable to zero, perform these steps.

  • Go to the Run Tab on the ribbon of the Visual Basic Editor
  • Press the Reset button.
how to reset the value of static variables in visual basic

This brings us to an end.

Thank you for reading.

Leave a Comment