Category Archives: VBA

Static & Public Declaration – Scope of variables in VBA

The very common way to initialize a variable in VBA is to declare using the ‘Dim’ function. But the Dim function exists only within a procedure or function, and it gets reinitialized every time the procedure is run.

Can we declare a variable to retain its value from a previous run?

Static Declaration within a procedure limits the variable to that procedure – but helps retain values of variables from previous runs, until forcefully reinitialized by explicit declaration using ‘ReDim’ or value setting.

Sub MySub( )
    Static Cnt As Integer
    Dim Msg As String
    Cnt = Cnt + 1
    Msg = “You’ve run the procedure ” & Cnt & ” times”
    MsgBox Msg
End Sub

Is there a way to declare a variable outside of a procedure?

Public Declaration helps declaring variables outside of the procedure, thus making a variable available to other modules within a workbook. Additionally, declaring a variable Public serves the purpose of Static property of retaining value from previous runs!

Option Explicit
Public Cnt As Integer

Sub MySub()
    Dim Msg As String
    Cnt = Cnt + 1
    Msg = “You’ve run the procedure ” & Cnt & ” times”
    MsgBox Msg
End Sub

Can we declare Array variables as Static & Public?

Yes! It’s the same way to declare array variables as single variables.

Public Cnt(1 To 10) As Integer

Sub MySub()   
    Dim Msg, Text As String   
    
    Cnt(1) = Cnt(1) + 1
    Text = “1:=” & Cnt(1)
    
    For i = 2 To 10
        Cnt(i) = Cnt(i) + Cnt(i – 1)
        Text = Text & ” | ” & i & “:=” & Cnt(i – 1)
    Next i
    
    Msg = “Checking the contents of the array – ” & Text
    MsgBox Msg
End Sub