"Hiding" Global Data in VBA

Mike Wolfe -

In his quintessential tactical guide to programming, Code Complete: Second Edition, author Steve McConnell includes the following advice for dealing with global data:

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.

-from Section 6.4 Reasons to Create a Class, p. 153

Under the sub-heading, "How to Reduce the Risks of Using Global Data," McConnell makes the following additional recommendation:

Create a well-annotated list of all your global variables Once your naming convention indicates that a variable is global, it’s helpful to indicate what the variable does. A list of global variables is one of the most useful tools that someone working with your program can have.

-from Section 13.3 Global Data, p. 343

Global data encompasses two different categories of program information:

  • Global Constants
  • Global Variables

Let's explore techniques for handling both.

Storing Global Constants

For global constants, I create a class module named C and set its hidden PredeclaredId attribute to True:

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.
No Longer SetMike Wolfe

This provides the following benefits:

  • All the global constants are in one "well-annotated list"
  • The global constants are discoverable by entering C. and then scrolling through the autocomplete list
  • You can use the MZ-Tools "Method Callers" feature to find all references to a global constant (without getting false positives like you would with a simple text-based variable name search)
  • You can easily refactor to pull the global constants from a database table (very handy if the same application is used by multiple clients)
  • Adding a new global constant is somewhat cumbersome, forcing me to think twice about whether it's really appropriate

Storing Global Variables

For global variables, I create a class wrapper around Access's built-in TempVars feature:

GenerateTVClass(): Auto-Create a TempVars Class Module
The culmination of my TempVars series, this article shows you how to generate a TempVars class module from a local table.
No Longer SetMike Wolfe

Creating and maintaining this class varies in key ways from the "C" class, but calling the class is very similar (the main difference is you use TV. instead of C.).

This approach has all the same benefits as the global "C" class above.

Additionally, by storing global variables in a separate class from global constants, there is a clear separation between data that is expected to change throughout the application's lifetime and data that is not.

[Overuse of] Global Data is Bad

Q: Why not just maintain a huge list of global variables and use them everywhere in your application?  

A: For the same reason your plumber doesn't do it:

Overuse of Global State
You wouldn’t control the water temperature in your entire house from a single location. Why would you write your code that way?
No Longer SetMike Wolfe

Cover image generated by DALL-E-3