TV: The TempVars Class Module

Mike Wolfe -

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 TempVars!MyVat).

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

  • Visibility
  • Type safety
  • IntelliSense
  • Compile-time checks for typos
  • No need to create an instance of the class

Sample Code

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

Usage

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.

Image by Pexels from Pixabay