TempVars: a new Access programmability facility for VBA and Macros

(This post was pre-recorded. I'll follow up on any comments when I'm back).

I thought it would be interesting to talk on this post about a minor, but interesting, piece of functionality that we are adding to Access 2007 - TempVars (for Temporary Variables). TempVars is a VARIANT store that only exists while a given solution/database is open, hence the "temporary" in its name. It allows you to set and get a VARIANT value for a given name - a variable name.

This store is helpful for solutions where you want to store temporary data (e.g the name of last form opened, user preferences, etc) that you need between VBA functions without the hassle of setting a table just for such transient usage. You can think of TempVars as big global VBA variables that work across the whole product - anywhere there is VBA or an expression.

We also expose the TempVars store as an enumerable collection, so you can enumerate the variables so you can figure out what's in it. Here's a simple example:

Function foo()

TempVars("Temp1").Value = 3

For Each tv In TempVars
    MsgBox tv.Name & " = " & CStr(tv.Value)
Next t

End Function

(This would show a message box with "Temp1 = 3").

As you noticed above, there are .Name and .Value properties for each TempVar, which are self evident. As far as the actual TempVars object is concerned, it has a .Remove (removes a single TempVar given its name) and .RemoveAll (removes all TempVars, clearing the store) method.

There is still, however, another aspect to TempVars: the Macro side of things. We are exposing three new macro actions that relate to TempVars:

(1) SetTempVar(name, expression), which allows macros to set a TempVar to a given value.

(2) RemoveTempVar(name), which removes the TempVar from the store.

(3) RemoveAllTempVars, which empties the TempVars store.

Along with the capability of getting the values of TempVars by using expressions (see example below) either in the arguments or in the conditions columns of macros, this effectively gives macro authors macro variables, which is obviously helpful and was a major draw back in the macro world.

Here's a sample macro where we use a TempVar as a simple local variable (apologies for the pixelated image):

Additionally, since there is a single TempVars store at play for the database/solution, you can use it to share data between your macros and VBA code, not only between VBA code. This allows a lot of interesting scenarios, which I'll talk about on my next posts.

Comments (13)

  1. AL says:

    Now this is an interesting idea.  Global variables without the need to stop your code and declare new variables.  

    Does it support intellisense, so we can view the entire TempVar collection in a dropdown box?  

  2. ThirdOfFive says:

    Hi Al,

    Unfortunantely, it doesn’t support Intellisense, in the same way that referencing controls by name also doesn’t support it (e.g. Controls("Foo").Bar).

  3. Now that Access 2007 Beta 2 Technical Refresh (B2TR) is out, you will notice that we changed the Switchboard

  4. Stevbe says:

    Can you get a TempVar from inside a query?

    SELECT * FROM tblTeam WHERE ID = TempVars("TeamID")


  5. ThirdOfFive says:

    Yes, you can. The syntax is slightly different though:

    SELECT * FROM tblTeam WHERE [ID]=TempVars!TeamID

    You can use TempVars anywhere expressions can be used.

  6. Stevbe says:

    kind of cool … I might consider dropping my custom code for *global* var handling. How well does it really clean up? Is it a true collection that you are resetting with the .Clear or is is just resetting an internal counter?


  7. ThirdOfFive says:

    Yes, it is a true collection. The moment you call .Clear we will erase all the information from memory and wipe it clean. It is also very fast since it’s an in-memory highly local data cache.

  8. Stevbe says:

    just curious …

    are you doing a Set TempVars = Nothing

    or are you doing an iterative .Remove?

  9. ThirdOfFive says:

    Actually, neither one. This is internally managed by Access in C++, so VB semantics don’t apply.

    What we do is clean up the memory associated with it and reset our internal structure that is used to keep track of these.

  10. Stevbe says:

    and while you are so forthcoming … is this now being written in C++ .NET or is it still unmanaged code?

  11. ThirdOfFive says:

    It is unmanaged code.

Skip to main content