Class Modules

Better Global Constants in VBA with Dot-Driven Development

My global constant "C" class--with PredeclaredId = True--is a great alternative to traditionally-declared global Public Const's.

Mike Wolfe

Mar 27, 2023 • 6 min read

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

The leading 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).

Dot-Driven Development

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.

Sample Code

Here's sample code for our base C class.

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 C.

The "C" Class

' --------------------------------------------------------------------------------------
' Name    : C
' Purpose : Provides dot-driven access to global constants.
' Author  : Mike Wolfe
' Source  : https://nolongerset.com/c-class/
' Date    : 7/24/2015
' Notes   - Making this a class module allows for more explicit scoping and easier
'             discoverability of global constants
'         - Chaining is enabled by calling Property Get on other class objects with
'             constants (class name prefixed with "const" by convention)
'         - The feature that gets around requiring a new declaration of this class is
'             setting the Attribute VB_PredeclaredId = True;
'             see https://nolongerset.com/setpredeclaredid/
' Usage   - No need to declare a New instance of this class, just type "c." and
'             choose from the IntelliSense dropdown list
'---------------------------------------------------------------------------------------
Option Compare Database
Option Explicit

Public Property Get AppName() As String
    AppName = "Global Constants Sample"
End Property

Public Property Get CountyID() As constCountyID
    Set CountyID = New constCountyID
End Property

Paste the above code into a new class module and name it C.

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.

Option Compare Database
Option Explicit

Property Get Adams() As Long: Adams = 1: End Property
Property Get Lackawanna() As Long: Lackawanna = 35: End Property
Property Get Montgomery() As Long: Montgomery = 46: End Property
Property Get Wayne() As Long: Wayne = 64: End Property

Paste the above code into a new class module and name it constCountyID.

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.

Option Compare Database
Option Explicit

Property Get Adams() As Long
    Adams = 1
End Property

Property Get Lackawanna() As Long
    Lackawanna = 35
End Property

Property Get Montgomery() As Long
    Montgomery = 46
End Property

Property Get Wayne() As Long
    Wayne = 64
End Property

Paste the above code into a new class module and name it constCountyID. Both samples in this section are functionally equivalent. Use whichever style you prefer, but don't try to use both. Besides, the compiler won't let you even if you try.

Setting the PredeclaredID Attribute

Copy and paste the above "C" Class code into a new class module
Name the class module C
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.