Excel & VBA

24 Feb 2018

Global and Local Variable Scope

Posted By

What is Variable Scope?

To put it simply, the visibility of variables to different procedures is referred to as the scope. Variables are commonly assigned a local, module or global level scope. Learn more about each type below.

Local Scope

A variable of constant declared within a procedure is visible only within that procedure. The variable can only be accessed in the procedure in which is declared, as shown in the example below.

In the example above, the OtherProcedure subroutine will produce an error as it cannot see the value of Pi (3.14) that was defined in a different subroutine. The value of Pi is defined on Line 3, and only exists in the LocalScope procedure.

Module Level Scope

A variable or constant declared by using the Private keyword outside of any procedures is visible to all procedures within that module. Consequently, this can reduce the number of times you need to declare a variable.

In the example above, both procedures will return the correct result as they can both see the value of Pi as at module level. As a result, if the value of Pi needed to be changed to 3.1415927 (for increased accuracy); you would only have to change it in one location.

Side note: You may have noticed a new keyword I used, Const. This is a variable modifier, abbreviation of ‘Constant’, which essentially tells VBA to not let any code change it’s value!

Global Level Scope

The last commonly used type would be global. A variable that is declared using the Public keyword outside any procedures is visible to all procedures within that module and all procedures in other modules i.e. globally!

Confusing Variable Scope

Figure 1: Confusing Variable Scope

This can however lead to confusion in larger projects with many procedures across many modules. It’s normally best practice to define variables locally or at a module level.

More information: Microsoft MSDN

Don't give up on your dreams... keep sleeping!

Leave a Reply