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.  

More to Follow

Future articles will 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

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