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.

A Safer Way to Use TempVars

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:

  1. Create a class named TV
  2. Set its PredeclaredID attribute = True
  3. Add a property Getter/Letter for each TempVar
  4. Set TempVar values in code like so: TV.MyVar = 42
  5. 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:

Referenced articles

The Global Form
Storing global variables on a hidden form has some distinct advantages over storing them in VBA.

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.

All original code samples by Mike Wolfe are licensed under CC BY 4.0