A Safer Way to Use TempVars
The TempVars object is an intriguing alternative to traditional global variables, but it has some shortcomings. Here's one way to work around them.
Microsoft Access has a TempVars collection that holds up to 255 TempVar objects.
The Problems with TempVars
Unlike traditional global constants, TempVar values survive a code reset. There are a few problems with TempVars, though.
- TempVars are not "visible" (as opposed to, say, a Global form)
- TempVars provide no type safety (they are of type
Variant) - TempVars do not appear in IntelliSense
- TempVars are prone to typos (no benefit from
Option Explicit) - TempVars cannot be directly called from queries
The Solution to (Most of) These Problems
One way to mitigate most of these shortcomings without sacrificing the TempVar benefits is to wrap them inside read/write properties of a VBA class module. Here's the basic idea:
- Create a class named TV
- Set its PredeclaredID attribute = True
- Add a property Getter/Letter for each TempVar
- Set TempVar values in code like so:
TV.MyVar = 42 - Retrieve TempVar values in code like so:
?TV.MyVar
Benefits of the TV Class Module Approach
This approach adds...
- Visibility
- Type safety
- IntelliSense
- Compile-time checks for typos
...to the TempVars object.
Further Reading
For more information, read these articles that cover related TempVars concepts:
- A sample TV class module to demonstrate this concept in action.
- A function you can use to incorporate TempVar values into queries.
- Code to populate a list box with the contents of the TempVars collection.
- Code to populate a local table with the contents of the TempVars collection.
- A design-time routine to generate a TV class module from a local table.
Referenced articles
Mike Wolfe
Image by Jason Dexter from Pixabay
UPDATE [2022-10-22]: Change the heading and text in the final paragraph to reflect that all the articles are now complete.
