Variable Scopes In Excel VBA

Variables should preferably be declared as local, i.e., within a procedure, so that each procedure can work as independently as possible from others. However, there are situations where variables must be declared with broader scopes.

Types of Scopes:

• Local variables:
  Declared inside a procedure (using Dim), these variables are only valid within that procedure. Outside the procedure, neither their name nor value is known. After the procedure finishes, their values are lost. Each time the procedure is called, the variables are newly declared and initialized.
• Static variables:
  Declared inside a procedure with the keyword Static instead of Dim. They are also only valid inside the procedure, but retain their values between calls to the procedure.
• Module-level variables:
  Declared at the top of a module (above all procedures) with Dim. These variables are valid throughout the entire module. Their values persist for the duration of the project and can be accessed or modified by any procedure within the module.
• Project-level variables:
  Declared at the top of a module with Public. These variables are valid throughout the entire VBA project. Their values can be accessed or modified by any procedure in any module during the project runtime.

Naming Conflicts and Shadowing:

• Local or static variables with the same name in the same procedure are not allowed.
• A module-level variable is hidden inside a procedure by a local variable with the same name.
• A project-level variable is hidden inside a module by a module-level or local variable with the same name.

Example: Different Scopes in Practice

Content of Module1:

Option Explicit
Public Px As Integer          ' Project-level variable
Dim Mx As Integer             ' Module-level variable
Sub Scope1()
    Static Sx As Integer      ' Static variable local to this procedure
    Dim Lx As Integer         ' Local variable
    Px = Px + 10
    Mx = Mx + 10
    Sx = Sx + 10
    Lx = Lx + 10
    MsgBox "Project-level Px = " & Px & vbCrLf & _
           "Module-level Mx = " & Mx & vbCrLf & _
           "Static Sx = " & Sx & vbCrLf & _
           "Local Lx = " & Lx
End Sub
Sub Scope2()
    Px = Px + 1
    Mx = Mx + 1
    MsgBox "Project-level Px = " & Px & vbCrLf & _
           "Module-level Mx = " & Mx
End Sub
Sub Scope3()
    Dim Mx As Integer
    Mx = Mx + 1
    MsgBox "Local Mx = " & Mx
End Sub

Content of Module2:

Sub Scope4()
    Px = Px + 5
    MsgBox "Project-level Px = " & Px
End Sub

Explanation:

• Px is project-level, accessible and modifiable from any module or procedure. Its value depends on how often and in what order procedures are called.
• Mx is module-level in Module1, accessible and modifiable by procedures within Module1 only. In Module2, it is unknown.
• In Scope3, the local variable Mx hides the module-level Mx.
• Lx is local to Scope1, recreated with each call, initialized to zero and incremented to 10.
• Sx is static local to Scope1, retains its value between calls, but is unknown outside that procedure.

Best Practices:

• Declare variables as local and non-static whenever possible to avoid unexpected dependencies on procedure call order.
• When broader scopes are necessary, ensure your program flow enforces a clear sequence .

Scope Rules for Constants and Enumerations:

• These same scope rules apply to constants and enumerations.
• Use Public Const for project-wide constants.
• Use Public Enum to define project-wide enumerations.