In VBA (Visual Basic for Applications), the Static statement is used to declare local variables in a procedure (Sub or Function) in a way that their values are preserved between calls to the procedure. Unlike regular local variables, which lose their value as soon as the procedure ends, Static variables retain their value between multiple calls of the same procedure.
Here’s how to use the Static statement:
Basic Syntax
To declare a static variable within a procedure, you use the Static keyword followed by the variable declaration:VBA
In this example, myVariable is a static variable of type Integer.
Sub MyProcedure()
Static myVariable As Integer
' Rest of the code
End SubExample Usage
Suppose you have a function that counts how many times it has been called:VBA
Each time CountCalls is called, callCount is incremented by 1, and its value is retained for the next call.
Function CountCalls() As Integer
Static callCount As Integer
callCount = callCount + 1
CountCalls = callCount
End FunctionScope of Static Variables
- The scope of a Static variable is local to the procedure in which it is declared. This means it is not accessible from other procedures.
- However, its lifetime is the same as that of the program, so it retains its value between calls until the program ends or the workbook is closed.
Static Variables vs. Module-Level Variables:
- A Static variable in a procedure is a good choice when you need to retain a value between calls to that procedure, but you don’t need the variable to be accessible from other procedures in the module.
- If you need a variable to be accessible from multiple procedures within the same module, consider using a module-level variable (declared outside of any procedure with Dim or Private at the top of the module).
Initializing Static Variables
Static variables are automatically initialized to the default value for their data type (e.g., 0 for numeric types, an empty string for String, False for Boolean, etc.) when the procedure is first called.Use Cases
Static variables are useful in scenarios like counting function calls, maintaining state information specific to a procedure, or caching results within a procedure for performance optimization. Remember, while Static variables can be very useful, they can also make the code more complex and harder to understand, especially in large projects. Use them judiciously and document their purpose well in your code comments.Unlock Your Potential
Excel
Access
Power BI
