Blog

In the previous tutorial, we learnt how to declare variables. In this part of the free VBA training series, we’ll explore the concept of Variable Scope and how it defines access to variables across different parts of a project.

In large VBA projects, understanding variable scope is crucial. If a developer doesn’t know where a variable is valid, data may be unintentionally modified or reused in other areas of the program. Therefore, understanding scope is one of the foundations of writing clean and error-free VBA code.

Types of Variable Scope in VBA

The scope of a variable determines in which parts of the VBA project it can be accessed. Generally, there are three main levels of scope in VBA:

1. Procedure Scope (Local)

A local variable is accessible only within the procedure where it is declared. These variables are usually declared using the Dim or Static statement.

Declaring a Local Variable with Dim

When a variable is declared with Dim inside a procedure, it remains in memory only until the procedure finishes execution. Once the procedure ends, the variable’s value is lost and will be reinitialised in the next run.

Sub Example1()
    Dim X As Integer
    X = 100
End Sub

Sub Example2()
    Dim X As String
    X = "Yes"
End Sub

In the example above, two variables with the same name but different data types are declared. Each one exists only within its own procedure and does not interfere with the other.

Declaring a Local Variable with Static

Local variables declared with the Static keyword retain their values between multiple executions of the same procedure, as long as VBA is running.

• If a runtime error occurs and isn’t handled, the value will reset.
• Stopping or closing VBA will also clear the value.
• Editing the module causes a reset as well.

Sub RunningTotal()
    Static Accumulate As Integer
    num = 2
    Accumulate = Accumulate + num
    MsgBox "The running total is " & Accumulate
End Sub

On the first run, Accumulate will be set to 2, and in subsequent runs, the new value will be added to the previous total.

2. Module Scope

If a variable is declared at the top of a module, outside any procedure, it becomes available to all procedures within that same module. Such variables can be declared using either Dim or Private.

Dim A As Integer
Dim B As Integer

Sub Example1()
    A = 100
    B = A + 1
End Sub
 
Sub Example2()
    MsgBox A
    MsgBox B
End Sub

In the example above, the variables A and B are accessible by both procedures within the same module.

3. Public Scope

Public variables have the widest level of access. They are available throughout all modules of a project. These variables must be declared in a standard module using the Public keyword.

' module1
Public SalesPrice As Integer
Public UnitsSold As Integer
Public CostPerUnit As Integer

' module2
Sub CDSales()
    SalesPrice = 12
    UnitsSold = 1000
    CostPerUnit = 5
    MsgBox "Gross Profit: £" & (SalesPrice * UnitsSold - (UnitsSold * CostPerUnit))
End Sub

In this example, the public variables declared in module1 are accessible in module2 without the need to redeclare them.

🟩 Quick Summary

• Dim (Local): Valid only within the same procedure.
• Static: Retains value between multiple runs.
• Private: Accessible throughout the same module.
• Public: Accessible from any module in the project.

Understanding these levels helps you write more structured and reliable VBA code.

Reference

Scope of variables in Visual Basic for Applications (Microsoft)