Excel VBA - Scoping Const Variable to be Accessed only by Current Project

Question

I have an Excel Add-in for storage of UDF's as well as a personal workbook for storage of subroutines.

Inside of the Add-in, I have a module for constants, where I have an entry for ideal gas constant:

Global Const r = 8.314 'Same result using Global or Public

This "global" constant can be accessed from all modules within the Excel Add-in and the personal workbook.

My issue is that I would like to be able to limit the scope of this variable to only the modules of the add-in and no other open projects. Otherwise, I need to keep track in all open projects as to what variables I'm declaring as Global. Eventually, I'll end up like NASA, getting my units confused...

Thanks all for your help in advance.

Answer 1

Global is an obsolete keyword; the only difference between Global and Public is that you can't use Global in a class module. Prefer Public for consistency.

Use the little-known Friend access modifier to make a member accessible everywhere inside the project it's declared in, but only within that project.

The caveat is that it's only usable in a class module, and only for procedures - you can't make a Friend Const.

A work-around could be to leverage the hidden VB_PredeclaredId class attribute and set it to True - create a new text file in Notepad, with this content:

VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
END
Attribute VB_Name = "StaticClass1"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit

Save the file with a .cls extension, then import it into your project.

Then you can rename it to something more meaningful, and expose a Friend Property Get member:

Friend Property Get R() As Single
    R = 8.314
End Property

And now you can use that value, but only within the project that contains this class. Because of the PredeclaredId attribute, you don't need to create a New instance:

Debug.Print StaticClass1.R ' 8.314

Sadly (?) the VB_GlobalNamespace attribute has no effect in VBA user classes, so the qualifying object (the predeclared class instance has the same name as the class module itself - that's exactly how UserForm1.Show can work) is required.

Caution: it can be tempting to store instance state (e.g. private module-level variables) on a default instance (especially in forms), but it should be avoided whenever possible. If you need to store state, consider working with a New instance of the class instead. Stateful default instances are more prone to bugs, notably because the object's lifetime is managed by the VBA run-time, not by user code. If the default instance is recycled/recreated, any state previously held is reset to whatever the design-time defaults are.

Note that class modules are private by default, which already makes them invisible to other referencing projects. You can change the Instancing property of the class to make it PublicNotCreatable, which will change the value of it VB_Exposed attribute to True:

Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = True

Public classes can be accessed from outside the project, but can't be created directly with the New keyword (changing the value of the VB_Creatable attribute has no effect in VBA). With a public/exposed class, referencing projects will be able to invoke the Public members, but not the Friend ones:

Public Property Get UseMeAnywhere() As Long
    UseMeAnywhere = 42
End Property

Friend Property Get YouOnlySeeMeInThisProject() As Long
    YouOnlySeeMeInThisProject = 42
End Property

Answer 2

A fix would be adding an Option Private statement to the declaration section of the module. This will limit the scope to only the project that the module sits within, not any open project. Link included below from Microsoft Docs for how to do this specifically.

For more information on scope and visibility see the link below from the Microsoft Docs:

https://docs.microsoft.com/en-us/office/vba/language/concepts/getting-started/understanding-scope-and-visibility

https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/option-private-statement

edit 04/22/2019: Took out the piece around Public to Global variable identifier as this didn't fix the problem.