The Static statement in VBA allows a variable to preserve its value between procedures or function calls. This means that the value of the variable will not be reset to its default value every time the code reaches the declaration statement, but will retain its value until the procedure ends. The Static statement is particularly useful when creating functions or subroutines that need to keep track of a specific value throughout their execution. It eliminates the need to use global variables, making the code more modular and easier to understand and debug.
Syntax
The syntax for the Static statement is as follows:
Static variableName [As DataType]
The variableName is the name of the variable that will be declared as ‘static’. It can be any valid variable name in VBA. The optional DataType specifies the type of data the variable will hold, such as Integer, String, or Boolean. If no DataType is specified, the variable will default to Variant.
VBA Static Statement Examples
Example 1: Keeping track of a counter
In this example, we will create a function that counts the number of times it has been called and displays the result as a message box.
Function Counter() As Integer Static count As Integer count = count + 1 MsgBox "The function has been called " & count & " times." End Function
Every time the ‘Counter’ function is called, the value of the ‘count’ variable will increase by one and will be preserved for the next time the function is called.
Example 2: Retaining user input
The Static statement can also be used to retain user input in a function. In this example, we will create a function that asks the user for their name and then greets them with their name.
Function Greeting() As String
Static name As String
If name = "" Then
name = InputBox("What is your name?")
End If
Greeting = "Hello " & name & "!"
End Function
The first time the ‘Greeting’ function is called, the ‘name’ variable will be empty and the user will be prompted to input their name. However, for subsequent calls, the ‘name’ variable will retain its value and the user will not be prompted again.
Example 3: Generating unique IDs
The Static statement can also be used to generate unique IDs for objects in VBA. In this example, we will create a function that generates a unique ID for each new object.
Function GenerateID() As String Static id As Integer id = id + 1 GenerateID = "OBJ_" & id End Function
Every time the ‘GenerateID’ function is called, the ‘id’ variable will increase by one, resulting in a unique ID for each new object.
Example 4: Keeping track of open workbooks
In this example, we will use the Static statement to keep track of the number of open workbooks in Excel.
Sub CountWorkbooks() Static numOfWorkbooks As Integer numOfWorkbooks = Workbooks.Count MsgBox "There are currently " & numOfWorkbooks & " workbooks open." End Sub
The ‘CountWorkbooks’ subroutine will count the number of open workbooks every time it is executed and display the result as a message box.
Example 5: Retaining filter settings
The Static statement can be used to retain filter settings in Excel. In this example, we will create a function that filters a column based on a specific criteria and retains the filter settings for the next time the function is called.
Function FilterColumn() As Range
Static filter As Variant
If filter = "" Then
filter = InputBox("Enter the criteria to filter the column.")
End If
Range("A1").AutoFilter field:=1, Criteria1:=filter
End Function
The first time the ‘FilterColumn’ function is called, the user will be prompted to input a filter criteria. However, for subsequent calls, the filter criteria will be retained from the previous call.
Important Notes & Remarks
- The Static statement can only be used inside procedures and functions, not in the global scope.
- The value of ‘static’ variables will not be preserved when the code is reset or the project is closed and reopened.
- The Static statement can only be used within the same procedure or function. It cannot be used to share values between different procedures or functions.
- If a Static variable is not initialized to a value, it will default to 0 or an empty string.
- The Static statement can only be used on variables declared within the same procedure or function. It cannot be used on variables declared in other modules or forms.
Conclusion
The Static statement in VBA is a very useful tool for retaining variable values between procedure or function calls. It helps to make the code more concise, efficient, and easier to maintain. By using the Static statement, you can avoid the use of global variables and keep your code modular. The examples shown in this post demonstrate just a few of the many ways in which the Static statement can be used in VBA.
