通过

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

  • Anonymous
    April 15, 2006
    The comment has been removed

  • Anonymous
    April 15, 2006
    The comment has been removed

  • Anonymous
    October 05, 2006
    Now that Access 2007 Beta 2 Technical Refresh (B2TR) is out, you will notice that we changed the Switchboard

  • Anonymous
    October 10, 2006
    Can you get a TempVar from inside a query? SELECT * FROM tblTeam WHERE ID = TempVars("TeamID") Steve

  • Anonymous
    October 10, 2006
    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.

  • Anonymous
    October 10, 2006
    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? Steve

  • Anonymous
    October 10, 2006
    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.

  • Anonymous
    October 10, 2006
    just curious ... are you doing a Set TempVars = Nothing or are you doing an iterative .Remove?

  • Anonymous
    October 11, 2006
    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.

  • Anonymous
    October 11, 2006
    and while you are so forthcoming ... is this now being written in C++ .NET or is it still unmanaged code?

  • Anonymous
    October 11, 2006
    It is unmanaged code.

  • Anonymous
    May 31, 2009
    PingBack from http://woodtvstand.info/story.php?id=8579

  • Anonymous
    June 02, 2009
    PingBack from http://woodtvstand.info/story.php?id=49767