What can you learn here?
- Public versus Private
- Variable visibility
- External references
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.
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
When that line is removed, then
Sub testScope() setScope Debug.Print moduleString End Sub
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
Option Explicit Public publicString As String Sub setPublic() publicString = "public test" End Sub
and then access it from our original module
Sub testscope() setPublic Debug.Print publicString End Sub
It correctly produces this output
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.
Private moduleString As String
is the same as
Dim moduleString As String
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
is the same as
Public Sub setPublic()
Whereas a private procedure cannot be seen outside its own module
Private Sub setPublic()
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 as any other object. The symptom of a missing reference is that the intellisense feature that shows an 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..
or even instantiate objects based on classes defined in ‘the other project’ and access public variables defined
Dim cTimer As cpProfiler.cProcTimer Set cTimer = cpProfiler.apProfiler_1.Timers("Proc:mloop")
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.
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
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
However, there is an exception to that. If you declare a variable as ‘static’, the variable will retain its value between procedure calls.
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
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.
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