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
- TempVars do not appear in IntelliSense
- TempVars are prone to typos (no benefit from
- 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:
Benefits of the TV Class Module Approach
This approach adds...
- Type safety
- Compile-time checks for typos
...to the TempVars object.
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.
UPDATE [2022-10-22]: Change the heading and text in the final paragraph to reflect that all the articles are now complete.