Understanding Scope

What can you learn here?

  • Public versus Private
  • Variable visibility
  • External references

What can you learn here

Public versus Private
Variable visibility
External references

Understanding Scopes

If you read the section on procedure arguments  you will have noticed the great capabilities in VBA (and all languages) for passing data between discrete procedures. Technically of course, all variables could be set up to be seen by all procedures, which would mean that we would never need to bother with any of that. But can you imagine what a mess that would be? That’s why variable scope (or visibility) is such an important concept to keep code clean and reusable. Some languages have the concept of namespaces to separate scope domains. VBA doesn’t really have that, since its scope is largely implied by the positioning of declaration.

Variable Scope within a module

If you want to create a variable that can be ‘seen’ by every Procedure in a Module, then you need to declare it (Dim) before you define any procedures. 
The code below will not be tolerated by the IDE because it references the variable localString in the Sub testScope.

</p>
<pre>Option Explicit
Dim moduleString As String
Sub testScope()
    setScope
    Debug.Print moduleString
    Debug.Print localString
End Sub
Sub setScope()
    Dim localString As String
    localString = "local"
    moduleString = "module"
End Sub</pre>
<p>

When that line is removed, then 

</p>
<pre>Sub testScope()
    setScope
    Debug.Print moduleString
End Sub</pre>
<p>

correctly outputs

</p>
<pre>module</pre>
<p>

Rule 1.Any procedure in a module can operate on any variable declared before the first procedure is defined in that module.Rule 2.A variable declared in a procedure can only be seen inside the procedure. 
Relating that to the stack discussion in Getting Started with argument passing, space for local variables are assigned on the stack and released when the procedure exits. It’s follows then that it would be impossible to see a variable for which memory only exists when a particular procedure is being executed. 

Accessing a variable in another module

Declaring a variable to be public, makes it visible to all modules in a project. 
So if we create a separate module with the following contents

</p>
<pre>Option Explicit
Public publicString As String
Sub setPublic()
    publicString = "public test"
End Sub</pre>
<p>

and then access it from our original module

</p>
<pre>Sub testscope()
    setPublic
    Debug.Print publicString
End Sub</pre>
<p>

It correctly produces this output

</p>
<pre>public test</pre>
<p>

Rule 3 A variable that is declared as Public in a module before any procedures are defined (it is illegal to try to declare public variables within procedures), is visible to every procedure and to every other procedure in every module in a project
This means that in the case of variables, Dim is equivalent to Private. 

</p>
<pre>Private moduleString As String</pre>
<p>

is the same as

</p>
<pre>Dim moduleString As String</pre>
<p>

See Getting Started with Classes for how this is used when declaring properties for classes

Scope of procedures

In the example above we were able to access the procedure setPublic from another module. This implies that 

</p>
<pre>Sub setPublic()</pre>
<p>

is the same as

</p>
<pre>Public Sub setPublic()</pre>
<p>

Whereas a private procedure cannot be seen outside its own module

</p>
<pre>Private Sub setPublic()</pre>
<p>

Rule 4 A procedure is by default visible by every procedure of every module in a project
Rule 5 A procedure declared as Private is visible to every other procedure in the same module but not to other procedures in any other module.

Constants, TypeDefs and eNum scope

The same rules apply to these structures as for variables. For example, Public TypeDef can be seen by every procedure of every module in a project

Referencing other components

By default, Excel is expecting only to reference a subset of the potentially referenceable objects that live on your machine. You can see what they are and add references to other objects by selecting tools/references in the IDE. That will bring up a dialog like this where you can add references to your project. 

Many of the projects on this site use additional components in addition to the default. The one above for example shows the references needed by Get Data From Google Docs. Once you have a reference like this defined you can access the objects they describe just like any other object. The symptom of a missing reference is that the IntelliSense feature that shows objects available property does not work, and of course the project cannot compile (assuming you have used Option Explicit) and of course will fail.

Rule 6 Always use Option Explicit to be sure to identify objects that are not visible to your project and add references as required.

Referencing Other Projects

If you are using an add-in to provide additional functionality, you may want to access objects specified in that add-in. Just as in the above example, you need to add a reference to the project.  In the example below, the workbook is using an optimization plugin (available here) to automatically profile its code. To be be able to access the objects and code in the add-in, it needs to make a reference to the add-in project name – in this case cpProfiler.

In this way, code can be accessed in ‘the other project’ – for example..

</p>
<pre>Call cpProfiler.autoProfileForm()</pre>
<p>

or even instantiate objects based on classes defined in ‘the other project’  and access public variables defined 

</p>
<pre>Dim cTimer As cpProfiler.cProcTimer   
Set cTimer = cpProfiler.apProfiler_1.Timers("Proc:mloop")</pre>
<p>

Rule 7 When writing .xlam add-ins, make sure that the project is called something sensible (as opposed to the default VBProject), so that a reference can be made to it by users of the add-in.

Static Variables

I mentioned earlier that space is allocated for local variables when a procedure is initiated. It is because of this that the value of variable is not ‘remembered’ between calls to a procedure. For example, in the code below, the result will always be ‘1’ no matter how many times the function is called

</p>
<pre>Private Sub testStatic()
    Debug.Print exStatic
    Debug.Print exStatic
End Sub
Private Function exStatic() As Long
    Dim x As Long
    x = x + 1
    exStatic = x
End Function
</pre>
<p>

result

1
1

However, there is an exception to that. If you declare a variable as ‘static’, the variable will retain its value between procedure calls. 

</p>
<pre>Private Sub testStatic()
    Debug.Print exStatic
    Debug.Print exStatic
End Sub
Private Function exStatic() As Long
    Static x As Long
    x = x + 1
    exStatic = x
End Function</pre>
<p>

Result

1
2

You might want to do something like this if you want to, for example, a function that generate a unique ID every time it is called. 
Rule 8Use static sparingly and only to achieve a specific well defined result. 

Summary

Rule 9 Always make all variables and procedures as private as possible. That way your code will be cleaner, and there will be easier traceability of what changes what when. Think long and hard before creating Public variables, and if you must, then at least minimize the places that their value can be changed.

All codes mentioned here are available here