VBA

Five Problems Working with Global Constants in VBA

Working with global constants in VBA is unnecessarily difficult, resulting in code that's harder to read and maintain than it needs to be.

Mike Wolfe

Mar 24, 2023 • 4 min read

VBA makes working with global constants unnecessarily difficult.

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

VBA's Case-Changing "Feature"

A common convention in many languages is to name global constants in all caps, such as:

APP_NAME
REPORT_FOLDER
LOG_FOLDER
DEFAULT_FONT_SIZE
TIMEOUT

That approach works very well for case-sensitive languages, like C# or Python, but it's less effective for a case-insensitive language like VBA. As if it weren't enough that VBA is case-insensitive, the IDE actively works against you by arbitrarily changing the casing of your existing identifiers–even if they're outside of the scope in which you are working.

For example, if you declare the following global constant:

Public Const TIMEOUT As Long = 30

And then later declare a local Timeout variable inside of a procedure:

Private Sub Foo()
    Dim Timeout As Integer
    
End Sub

Then VBA will automatically change every identifier named "timeout" in your entire project! Here's the end result:

Public Const Timeout As Long = 30

Private Sub Foo()
    Dim Timeout As Integer
    
End Sub

Frustrating, amirite?

It's a Variable. It's a Constant. It's a Function.

You can't tell by glancing at the following code whether the identifier "Timeout" represents a variable, a constant, or a function with no required parameters:

Debug.Print Timeout

If I were reading the above code, I would need to go searching for the definition of Timeout. That would require switching contexts and loading a different part of the code into my brain, which is inefficient.

Lack of Discoverability

If you think writing code is hard, you should try taking over maintenance on someone else's code. Or joining a team with large applications already in place. Getting up to speed on a new code base is exceedingly difficult.

In a typical code base, the only way to learn what all the global constants are is to search for "Public Const". ... Every time you want to see if some global constant exists.

And if it doesn't exist and you need to add a new one, you have to figure out where to add it. And if the answer to that question is not obvious, you'll pick some arbitrary standard module to throw it in. Which increases the project's technical debt and makes things harder for the next person.

Global Namespace Pollution

What's the "global namespace"? That's the list of every available identifier–every variable, constant, enum, subroutine, function, method, property, etc.–that can be invoked directly anywhere in your code.

In Access VBA, the global namespace is full of stuff.

Don't believe me? Go to a new line in the Immediate Window ([Ctrl] + [G]) and press [Ctrl] + [Space]. The list that just popped up for you is a list of every item available in the global namespace. Hold the down arrow to scroll through the list.

It's a looooooooooooooooooooooooooooooooooong list.

It took me roughly 90 seconds to scroll through every item in the list.

The global namespace is polluted enough. We don't need to add to it if we can avoid it.

Global Constants are Easy to Add...A Little Too Easy

Don't get me wrong. A global constant is a big improvement over magic strings and magic numbers. But you really shouldn't have that many global constants in the first place.

Remember, a global constant is a value that you have hard-coded.

By definition, that's a value that your users cannot change. Sometimes that makes sense. But oftentimes it's better to swap out those hard-coded values for configuration settings.

Of course, managing configuration settings is more difficult and time-consuming for developers than using a hard-coded constant. And when we're pressed for time, it can be all too easy to simply add a global constant and call it a day. Especially if we rationalize the decision by telling ourselves it's only temporary.

A Better Way

In my recent projects, I've adopted a different approach to global constants that addresses all of these drawbacks. I'll demonstrate that in a future article. Stay tuned...

UPDATE [2023-09-06]: I forgot to post the link to the "future article" (which has now been up for several months). Here's the aforeshadowed article, Better Global Constants in VBA with Dot-Driven Development.