Scope of Variables
 

This section shows you the important concept of the scope of variables with an example, or technically “which parts of a program have access to which other parts?”

You have learned in previous example how to declare a variable with a Dim statement and here is another example.
(You can also download the worksheet). When a variable such as MySalary is declared within a Sub, it is called procedure-level variable or local variable.

Sub Test_1()

            Dim MySalary as Currency

            MySalary = 7000

End Sub 

Sub Test_2()

    MsgBox "My monthly salary is " & MySalary

End Sub

When you run Test_2, the message box does not display “My monthly salary is 7000”. Test_2 has no memory that MySalary was 7000 in Test_1. This is because only Sub Test_1 that contains the variable MySalary recognizes it. Since Test_2 does not know the value of MySalary variable in Test_1, Test_2 can have its own MySalary variable. Now you create it in Test_3. You see that Test_3 knows only that its own created salary variable, defined as 8000.

Sub Test_3()

    Dim MySalary As Currency

    MySalary = 8000

    MsgBox "My monthly salary is " & MySalary

End Sub

To conclude, local variables in different Subs can have the same names, but they have different memory locations. In another word, several Sub procedures can have a variable called MySalary, but because every variable is local to its procedure they all lead independently of each other and can different values.

What if you want different Sub procedures to have access to common variables? You can declare these variables at the top of a module, before any subs. You have 2 choices by either declaring at the top of a module with the Dim keyword (an alternative to Dim is the Private keyword) or the Public keyword.

Dim MySalary as Currency or Private MySalary as Currency

Public MySalary as Currency

The variable with Dim or Private keyword is a module-level variable, which means that every sub in the module has access to the variable MySalary. The variable with Public keyword is a global-level variable (or called public variable), which means that all modules in the entire project have access to the variable MySalary. A global variable has project-level scope.

If you declare a variable to have module-level or project-level scope, you should not declare the same variable inside a sub with a Dim statement as in the next example. If run Test_6, the message box will not display "My monthly salary is 7000”, but it will display "My monthly salary is 0”. This is because the Dim statement in Test_6 creates its local version of MySalary that overrides the public version. This local version is initialized to 0 by default.

Public MySalary As Currency

Sub Test_5()

    MySalary = 7000

End Sub

Sub Test_6()

    Dim MySalary As Currency

    MsgBox "My monthly salary is " & MySalary

End Sub