Variable Scope in Excel VBA

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.

Code:

Option Explicit  

Private Sub CommandButton1_Click()

  sub1

  sub2

End Sub

Place the two sub in a module.

Add the following code.

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.

Code:

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:

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.