There are (at least) five problems with using standard global constants in VBA:
- VBA's case-changing "feature" makes the all-caps convention unreliable
- Optional parentheses make it hard to distinguish between functions and variables/constants
- Lack of namespaces hinders discoverability
- Global namespace pollution
- It's too easy to add new global constants
I'd like to propose a better way.
C: The Global Constant Class
I stopped using
Public Const declarations more than seven years ago.
In their place, I use a class module that holds the sort of global, read-only values that would otherwise be declared using
Public Const statements. The name of this class module is simply the letter
C (for Constant).
Additionally, I set this class module's hidden PredeclaredId attribute to True so that I do not have to declare a new object variable in order to use the class. This lets me treat the class module as a pseudo-namespace (addressing #3), which avoids further pollution of the already-crowded VBA global namespace (addressing #4).
Every reference to a global constant begins with
C. optionally followed by the constant's category and then the constant's name. For example:
Debug.Print C.AppName Global Constants Sample Debug.Print C.CountyID.Wayne 64
C. makes it very obvious that what follows is a global constant (addressing #2). This also means we don't have to rely on the traditional all-caps naming convention, since the VBA IDE actively works against us if we try to do that (addressing #1).
The biggest complaint most developers will have is that it it is more difficult to add a new global constant. Instead of a single line of code, such as:
Public Const APP_NAME As String = "Global Constants Sample"
You would need to use a Property Get statement, which is three lines:
Public Property Get AppName() As String AppName = "Global Constants Sample" End Property
However, I propose that this added friction is a feature not a bug. Overuse of global state is a leading contributor to technical debt. Making it harder to add new global constants forces you to evaluate your choice and make sure you've considered alternatives, such as a user-configurable value or a single-row table (addressing #5).
I recently read this term in Mark Seemann's excellent book, Code that Fits in Your Head. The idea behind dot-driven development is that you let the IntelliSense guide you to discovering how an API works.
I have an application used in several PA counties. Certain parts of the application have been customized based on the needs of individual counties. If a new employee wants to know which counties we currently support, they need only type
C.CountyID. and the IntelliSense will show them a list of available counties and return the CountyID of whichever county they select.
To make this work, we use a naming convention to add additional global constant "sub-classes". I put sub-classes in quotes so as not to confuse this technique with subclassing in the general OOP sense. I simply mean the next level down in our global constant hierarchy.
Here's sample code for our base
Yours will look completely different, though I highly encourage you to name it a single letter for convenience; both
C(for constant) or
G (for global) are good options. The samples below all assume a class named
The "C" Class
The "constCountyID" Class
I like to use statement separator tokens (the colon
: character) to combine lines when I have multiple single-line-of-code properties or procedures.
Some programmers don't like that style or are unfamiliar with it. Here's the exact same
constCountyID class code, but formatted in a more traditional style. Use one or the other, but not both.
Setting the PredeclaredID Attribute
- Copy and paste the above "C" Class code into a new class module
- Name the class module
- Copy and paste the "Full Code" from my SetPredeclaredID article into a standard code module
- Save your code
- Run the following command in the Immediate window:
SetPredeclaredId "C", True
One of Steve McConnell's "Reasons to Create a Class"
In Steve McConnell's legendary tome, Code Complete: Second Edition, he includes the following paragraph in section 6.4 "Reasons to Create a Class":
Hide global data If you need to use global data, you can hide its implementation details behind a class interface. Working with global data through access routines provides several benefits compared to working with global data directly. You can change the structure of the data without changing your program. You can monitor accesses to the data. The discipline of using access routines also encourages you to think about whether the data is really global; it often becomes apparent that the “global data” is really just object data.
I have been using this technique for over seven years now. I first purchased Steve's book many years ago, though I can't remember exactly when. I honestly don't know whether I learned this technique from Steve's book or whether I stumbled into it independently. Either way, I feel better sharing it with you knowing it's got Steve's implicit blessing.
Cover image created with Microsoft Designer