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.