Excel VBA variables scope is split into three parts
1) Procedure level scope
2) Module level scope
3) Public level scope
Table of Contents
1) Procedure level VBA variables scope
Procedure variable/Procedure level VBA variables scope defines that variables can be accessible only within the Procedure where it is declared. Procedure may be function or sub procedure. Procedure level VBA variables can’t be accessed by other procedures. The lifetime of the variables lies within the procedure and value of the variable destroyed at the end of procedure
Example 1
Output
Variable i value in Procedurecheck1 is 5
Variable i value in Procedurecheck2 is
Go to Developer tab, insert a Module by Insert->Module and copy paste the above code. In the above example we have two subroutines Procedure_Scope_1 and Procedure_Scope_2. Procedure_Scope_1 subroutine variable i is declared, assigned a value 5 in the second line and printing the value in the third line. When the compiler reaches the End sub the variable i is destroyed. In the second procedure Procedure_Scope_2, we are trying to access and print the variable using Debug.Print but the variable i in Procedure_Scope_2 is initialized with Null value (vbNullstring) because it can’t access the variables present in the other subroutine.
Note: If you want to view the debug.print output activate the Immediate window panel by View-> Immediate Window or Ctrl+G
2) Module level VBA variables scope
Module variable/ Module level VBA variables scope defines that variable can be accessed only by the Functions or sub procedures within the module where the variable is declared. Module level variable can’t be accessed by other modules. The scope of the variables lies within the module and value of the variable destroyed when compiler reaches the end of the Module. Use Dim/Private to declare Module level variable. Since the module level variable is not belonging to any procedure it retains its value even after the procedure which changes it value has terminated.
Example 2
Output
Value of var1 in Module_Scope_1: 20
Value of var1 in Module_Scope_2: 35
Go to Developer tab, insert a Module by Insert->Module and copy paste the above code in the Module1. In the above example, I’ve declared a variable var1 at the module level which can be accessed only by Functions and subroutines present inside the Module1.
In the “Module_Scope_1”, I’ve assigned a value 20 to the variable var1. When you execute the Module_Scope_1 it prints the value 20. After it prints the value I want to add 15 to variable var1 and store it in the computer memory. When you execute the Module_Scope_2 procedure it prints the value 35. Here the changes made to the variable var1 in the Module_Scope_1 affects the Module_Scope_2. If you execute the Module_Scope_2 first then it display value 0 because no value is assigned to var1 inside the Module_Scope_1.
3) Public level VBA variables scope
Public variable/ Public level VBA variables scope defines that variable can be accessed by the Functions or sub procedures of all the modules within the project and outside the project as well by adding the project as reference in VBA Editor Tools-> Reference. To declare a public level variable you need to prefix the word “public” before the variable name. If you want the public variable to be accessed only within the project then you need to add statement “Option Private Module” at the top. Otherwise, it can be accessed by any other projects which referring this project.
Example 3
Output
Value of var1 in Public_Scope_1: 20
Value of var1 in Public_Scope_2: 35
I’ve used statement Option Private Module at the top of the code which prevents public variable var1 to be access by other VBA project. In the procedure Public_Scope_1, public variable var1 is initialized with value 20 and it print the same in the immediate window tab. Later, 15 gets added to public variable var1 so it’ll be changed to 35. In the procedure Public_Scope_2, I’m just printing the value stored in the variable var1(35).