This article is part of the series, A Safer Way to Use TempVars.
TempVars are an alternative to VBA global variables.
Unlike global variables, they retain their value when code execution resets–whether that's due to an unhandled error or a manual reset of the code from the VBA IDE (Run > Reset).
Unfortunately, they lack a few benefits of global variables: visibility, type safety, IntelliSense, and typo checks (e.g., setting
TempVars!MyVar but getting
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.
Type Safety Briefly Explained
TempVars are not type-safe because their values are always
Variant data types. For example, the following code will not raise any errors at compile time or runtime:
TempVars!MyVar = CLng(42) TempVars!MyVar = CStr("towel")
However, the following VBA code will throw a "Type mismatch" error at runtime when we try to assign a non-numeric String value to a variable with the Long datatype:
Public Sub MyVarError() Dim MyVar As Long MyVar = 42 MyVar = "towel" End Sub
If we have defined
MyVar as a long integer, it's nonsensical to try to assign a string value to it. Type safety alerts us to such foolishness.
Benefits of a PredeclaredID Class Module Approach
- Type safety
- Compile-time checks for typos
- No need to create an instance of the class
The text below was created by using the File > Export File...menu option.
To use it in your project, you will need to copy and paste it into a text file named TV.cls (the actual name does not matter), then use File > Import File... to import the file into VBA. You cannot simply copy and paste the text into the VBA IDE, because the
PredeclaredId=True line will be ignored. See here for background on
Attribute VB_PredeclaredId = True.
VERSION 1.0 CLASS BEGIN MultiUse = -1 'True END Attribute VB_Name = "TV" Attribute VB_GlobalNameSpace = False Attribute VB_Creatable = False Attribute VB_PredeclaredId = True Attribute VB_Exposed = False ' ------------------------------------------------------ ' Name : TV ' Purpose : Adds type safety and compile-time checking to the TempVars collection. ' Author : Mike Wolfe ' Source : https://nolongerset.com/tv-class/ ' Date : 9/20/2022 ' Notes : TempVars is a collection of up to 255 name-value pairs ' whose values survive code resets ' ------------------------------------------------------ Option Compare Database Option Explicit Public Property Let MyVar(Value As Currency) 'When assigning a value, we set it to a TempVar object with the ' same name as the class property TempVars("MyVar") = Value End Property Public Property Get MyVar() As Currency 'When retrieving a value, we get it from the same TempVar object we ' assigned it to in the accompanying Let statement MyVar = TempVars("MyVar") End Property 'We use line continuation characters (`:`) to keep our TV class module compact Public Property Let MyOtherVar(Value As Integer): TempVars("MyOtherVar") = Value: End Property Public Property Get MyOtherVar() As Integer: MyOtherVar = TempVars("MyOtherVar"): End Property 'To support optional values for variables that have not been set yet, we use the private GetTV() function Public Property Let MyCoolVar(Value As Double): TempVars("MyCoolVar") = Value: End Property Public Property Get MyCoolVar() As Double: MyCoolVar = GetTV("MyCoolVar", 123.45): End Property 'By using Variants for the default value and return type, we give up some ' type safety for the convenience of having a single function Private Function GetTV(VarName As String, DefaultValue As Variant) As Variant Dim Val As Variant Val = TempVars(VarName) If IsNull(Val) Then GetTV = DefaultValue Else GetTV = Val End If End Function
If you followed the import instructions correctly above, you should be able to type
?TV. into the immediate window and see the three public properties of the class module in the IntelliSense dropdown:
- Visibility? Check.
- Type safety? Check.
- IntelliSense? Check.
- Compile-time checks for typos? Check.
- No need to create an instance of the class? Check.
It is a bit more work up front than just typing
TempVars!MyVar = "something", but the added benefits of this approach will help with the long-term maintenance of your application.
If this seems like too much work, don't worry! In a future article, I will provide a routine you can use to generate this entire class module from the contents of a local table.