Scope in Visual Basics

Definition of Scope

The scope of any programming language implies the area of code where the variables will be identified, accessed, and used. Every variable has a scope associated with it. The scope of a variable is defined at the time the variable is declared.

The available scope for a variable can be the either of the three scopes i.e., procedure, module, and public:

 Procedure Level - Procedure is also known as a local variable. They are only declared within the specific procedure or function.

 Module Level - A module-level variable is accessible to only the module it is declared in.

 Global Level – Global is also known as public variable. A public module-level variable is accessible to all modules present in the worksheet.

Procedure Level

A local variable or procedure level variable is recognized only within the specific procedure or function in which it is declared and is not accessible outside the subroutine. Hence, Local variables can only be used in the procedure in which they are declared. When the procedure or function ends, the variable is removed automatically, and the memory is discharged. The advantage of using local variables is that we can use the same name in different subroutines without any discord.

Declaration Statement

Dim

Scope

Only available in the sub in which it is declared.

Lifetime

It is destroyed at End Sub of the Sub in which it was declared, or end statement.

Types

A local variable is declared either with a Dim or Static statement. One can use the Dim, Static, or Private statement within a specific subroutine or function.

1. Dim

To explicitly declare local variables in VBA, Dim statement is being used. In Dim statement, the variable only persists in existence as long as the procedure in which it is declared is running. Usually, when the procedure is finished, the values of the local variables declared in the procedure are not preserved, and the memory allocated to those variables is released. The next time when the same procedure is executed, all its local variables are reinitialized.

The most common way to declare a local variable is to use the Dim statement between the Sub and End Sub statements.

In the following macros, the variable ‘LVar’ is declared in each of the modules. Wherein each variable ‘LVar’ is independent of the other, i.e., the variable is only accessible within its specific subprocedure.

Program 1

 Sub DimLocalVariableExample1()
       Dim LVar As Integer
       ' Local variable, with Dim Scope.
       LVar = 10
       MsgBox "The value of LVar is " & LVar
    End Sub 

Output

Program 2

 Sub DimLocalVariableExample2 ()
       Dim LVar As String
       ' Local variable, with Dim Scope.
       LVar = "Hello VBA"
       MsgBox "The value of Variable is " & LVar
    End Sub 

Output

2. Static

A local variable declared with the Static statement remains in existence the whole time the VBA is underuse. The static variable is reset when any of the following cases happen:

• The macro generates an untapped run-time error.
• Visual Basic is halted.
• You quit Microsoft Excel.
• You can change or edit the VBA module.

Program

Sub StaticLocalScope()
       Static Factorial
       Factorial = 1
       ' Static Local variable that will hold its value even after the module has finished its executing.
       num = Application.InputBox ( prompt:="Enter a number: ", Type:=1)
       For i = 1 To num
       Factorial = Factorial * i
       Next
        MsgBox "The factorial of " & num & " is " & Factorial
    End Sub 

Output

Module scope

A “module-level” variable that is accessible amongst all the procedures available in a module sheet. A module-level variable is visible or available to all the procedures in that module, but it is not visible to procedures present in another module. It remains in existence the entire time VBA is running until the module in which it is declared is edited or modified. 

Declaration Statement

Private or Dim

Lifetime

Destroyed either when the workbook is closed or a “stop” is hit in the VBE, or a new sub is added to the module or End statement.

Scope

Only available in the module in which it is declared.

Types

Module-level variables can be declared either with a Dim or Private statement above the first procedure definition at the top of the module. They cannot be declared within the procedure. 

In Module level variable Dim statement, this is equivalent to a Private statement. Hence, there is no difference between Dim and Private. But, generally, Private statement is used for Module, as Dim is particularly used for local variables only. This makes the scope of a variable clearer.

Program 1

Sub ModuleScopeExample1()
 'Declaring the variable in another procedure
 Dim Var1 As Double
 'Declaring the variable in another procedure
 Private Var2 As Double
 End Sub
 Sub ModuleScopeExample2()
    'Calling the Var1 and Var2 in another procedure
     Var1 = 100
     Var2 = 200
     'The output will be null as private scope
     MsgBox "The value of Var1 is " & Var1
     MsgBox "The value of Var2 is " & Var2
 End Sub

Output

Global Scope

A “Global level” variables are also known as public variable, project level or global variables. These variables are visible or accessible to all modules present in the project and have the broadest scope of all variables.

Project level variables are declared in the “General Declarations Area” i.e., at the top of the module, above the first procedure definition. It cannot be declared within a procedure. A public variable is always declared with a "Public" statement.

Declaration statement

Public

Scope

Available to all subs in the project.

Lifetime

Destroyed at End Sub procedure in which the variable was declared.

Program

 ‘Declaring the public variable in sub procedure
 Sub GlobalScopeExample1()
     Public var As Double
 End Sub
 ‘Calling the above variable in another sub procedure
 Sub GlobalScopeExample2()
     var = 110
     MsgBox "The value for the variable is " & var
 End Sub 

Output