Variable Scope

The scope of a variable in Excel VBA determines where that variable may be used. You determine the scope of a variable when you declare it. There are three scoping levels: procedure level module level and public module level.

Place a command button on your worksheet and add the following code lines:

1. Place the two procedures (a procedure is either a sub or a function) into a module. In the Visual Basic Editor click Insert Module. Add the following code lines:

2. Result when you click the command button on the sheet (call the two subs):

Explanation: the variable txt has scope procedure level because it is declared in the procedure (between Sub and End Sub). As a result you can only use this variable in sub1. The variable txt cannot be used in sub2.

3. When you want a variable to be available to all procedures in a module you are saying you want the variable to have module level scope. You need to declare the variable in the General Declarations section (at the top of the module). Slightly adjust the code as follows:

4. Result when you click the command button on the sheet:

Explanation: the variable txt can now be used in sub2. Module level is used interchangeably with private module level. That is because by default variables declared with the Dim statement in the General Declarations section are scoped as private. You can also scope a variable as public. Read on.

5. By using the Public keyword your variable will be available to all procedures in all modules in a workbook. This is called public module level scope. Slightly adjust the code as follows:

Explanation: now you can create a new module and place a sub called sub3 into this module. Use the same code as sub2. Add sub3 to your command button code. When you click the command button on the worksheet you will get three message boxes saying “Variable can only be used in this procedure” (see downloadable Excel file).