In this post, you’ll learn about Variable scope and how to use it with-in your Excel workbook using Excel VBA.
Variable Scope in Excel VBA
The Variable scope or the scope of a variable determines where that variable can be used.
Scope of the Variable is of three levels.
- Procedure level
- Module level
- Public module level.
Let’s see them in Detail
First, place a command button on your worksheet
Add the following code lines to the button.
Option Explicit Private Sub CommandButton1_Click() sub1 sub2 End Sub
Place the two sub in a module.
Add the following code.
Open Explicit Sub sub1() Dim txt As String Txt= "Procedure Level, Module Level and Public level " MsgBox txt End Sub Sub sub2() MsgBox txt End sub
Click the command button on the sheet.
Two popups appear on the screen.
On is the MsgBox and the other is an error message.
This is because The variable txt has scope only in the procedure level that is, it is declared the between Sub and End Sub. Therefore, it cannot be used in Sub2.
Now let’s see the module level scope.
Module level scope
For a variable to have a module-level scope, it needs to be declared in the General Declarations section.
Open Explicit Dim txt As String Sub sub1() Txt= "Procedure Level, Module Level and Public level" MsgBox txt End Sub Sub sub2() MsgBox txt End sub
Now, when you click the command button, two poups are thrown. And both are MsgBox of sub1 and sub2 with the txt.
Public module level
Using the Public keyword, the scope can be made available to all the modules in the workbook.
Code: Open Explicit Public txt As String Sub sub1() Txt= "Procedure Level, Module Level and Public level" MsgBox txt End Sub Sub sub2() MsgBox txt End sub
Now let us add one more sub named sub3 to a new module. Add sub3 to your command button. Now run sub3 with the same code of sub2 which will in turn gives you the same result as befor.