VBA Scope And Lifetime

Scope, visibility, accessibility, and lifetime are related concepts that describe when and where a variable can be used. Scope and lifetime are determined by where a variable is declared and the modifier used to declare it.

Scope And Visibility

Scope and visibility refer to the area in a program where a name is bound to a variable. A name is bound to a variable when using that name refers to that particular variable. It is possible for the same name to exist in another scope but refer to a different variable. There are four scopes in VBA: Public, Project, Module, and Procedure.

Scope	Description
Public	Entity is available everywhere (in any project, in any module, and in any procedure). Variables declared outside a procedure with the Public modifier are in the Public scope.
Project	Entity is available in any module within the project. When Option Private Module is declared, Public members are in Project scope.
Module	Entity is only available within the module in which it is declared. Variables declared outside a procedure with the Dim or Private modifier are in the Module scope.
Procedure	Entity is only available within the procedure in which it is declared. Variables declared inside a procedure and a procedure's parameters are in the Procedure scope.

Controlling Scope

A variable's scope is determined by where it is declared and what access modifier is used to declare it. Dim and Const can be used inside and outside procedures. Public and Private can only be used outside procedures. Variables can be declared inside procedures using Dim or Static. Static has the same scope as Dim inside a procedure but Static changes a variable's lifetime.

Note: The "Global" modifier has the same effect as the "Public" modifier but is only available in standard modules. The Public modifier should be used instead.

Modifier	Location	Scope	Visibility	Lifetime
Dim	Inside procedure	Procedure	Inside procedure	During procedure execution
Static	Inside procedure	Procedure	Inside procedure	While application is running
Dim	Outside procedure	Module	Inside module	While application is running
Const	Inside procedure	Procedure	Inside procedure	During procedure execution
Const	Outside procedure	Module	Inside module	While application is running
Private	Outside procedure	Module	Inside module	While application is running
Public	Outside procedure	Public	Everywhere	While application is running
Private Const	Outside procedure	Module	Inside module	While application is running
Public Const	Outside procedure	Public	Everywhere	While application is running

Option Explicit

Public a  As Long 'Public scope
Private b As Long 'Module scope
Dim c     As Long 'Module scope

Public Const d  As Long = 3 'Public scope
Private Const e As Long = 2 'Module scope
Const f         As Long = 1 'Module scope

Public Sub Example()

    Dim g    As Long     'Procedure scope
    Static h As Long     'Procedure scope
    Const i  As Long = 0 'Procedure scope

End Sub

Declaring Option Private Module makes all Public scope members within the module Project scope.

Option Explicit
Option Private Module

'Message and Example are only available inside the project containing this module.

Public Message As String

Public Sub Example()
    Debug.Print Message
End Sub

Name Shadowing

Name Shadowing is when a variable in a lower scope is given the same name as a variable in a higher scope. The name of the variable in the higher scope is shadowed by the same variable name in the lower scope and is no longer visible to the lower scope. The name is bound to the variable in the most immediate scope and is no longer bound within that scope to the variable in the higher scope. The shadowed variable in the higher scope can still be accessed however by using the variable's fully qualified name. To fully qualify a variable name include the name of the module where the variable is declared followed by the dot (".") operator followed by the variable name.

'Standard Module: Module1

Option Explicit

Public i As Long

Public Sub Example1() 
    
    'Public i is shadowed by local i
    Dim i As Long 
  
    'Public i not affected
    i = 1 

End Sub

Public Sub Example2(i As Long) 
    
    'Public i is shadowed by parameter i

    'Public i not affected
    i = i + 1 

End Sub

Public Sub Example3()

    'Public i is shadowed by local i
    Dim i As Long 
    
    'Public i not affected
    i = 1 
    
    'Public i is affected using fully qualified name
    Module1.i = 2 

End Sub

Variable Lifetime

The Lifetime of a variable is the duration of time where a variable exists in memory. The lifetime of a variable with Procedure scope is the duration the procedure is executing. The variable is loaded in memory when the procedure is called and is unloaded from memory when the procedure finishes. The lifetime of a variable with Module, Public, or Project scope is the duration the application is running. Variables with Module, Public, or Project scope are loaded in memory when the application starts and are unloaded from memory when the application is terminated.

Static

The lifetime of variables declared Static is the duration the application is running. Variables declared Static keep their values between procedure calls. Procedures declared Static keep the values of all their local variables between procedure calls. Static does not change the scope of local procedure variables, which is still procedure scope. Static only changes the lifetime of variables to extend beyond the duration the procedure is executing.

Option Explicit

Public Sub TestStaticVariable()

    'i is loaded in memory when procedure called and unloaded when procedure finishes
    Dim i As Long
    i = i + 1
    Debug.Print i

    'j remains loaded in memory while the application is running
    Static j As Long
    j = j + 1
    Debug.Print j

End Sub

Public Static TestStaticProcedure()

    'i remains loaded in memory while the application is running
    Dim i As Long
    i = i + 1
    Debug.Print i

End Sub