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

Advertisements

One thought on “Static & Public Declaration – Scope of variables in VBA

  1. JP

    You don’t declare Static variables outside a procedure. If you want a global variable that can be used in any procedure, just declare a Public variable at the top of a standard module, like this:

    Public my_variable As String

    Excel does have a rudimentary query builder. In Excel 2003, go to Data > Import External Data > New Database Query and make sure “Use the query wizard” is checked.

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s