GenerateTVClass(): Auto-Create a TempVars Class Module

This article is part of the series, A Safer Way to Use TempVars.

A dedicated TempVars class module (with PredefinedId = True) has many benefits:

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

Writing such a class by hand isn't difficult, but it is tedious.  Luckily, there are easier ways to generate boilerplate code in Microsoft Access.

Starting Point

Before you get started, you will need a table with at least three fields:

• VarName: name of the TempVar
• VarTypeName: name of the TempVar's type (e.g., String, Integer, etc.)
• VarValue: a default value for an unset TempVar; this field is OPTIONAL

To create a blank table, you can run the CreateTempVarsTable routine that I wrote about here.  Once you have the table, populate it with the TempVars you would like your class module to handle.  For example:

The Approach

The subroutine follows a four-step approach:

1. Build the header
2. Build the Let/Get properties from the source table
3. If needed, build a private function to support TempVars with default values
4. Generate a class module named TV from the string we just built

BEWARE: In step 4, any existing class or standard module named "TV" will be overwritten without warning... Consider this your warning.

The GenerateTVClass Subroutine

This procedure has the following dependencies:

• UpsertClassModule

' ----------------------------------------------------------------
' Procedure : GenerateTVClass
' Date      : 10/21/2022
' Author    : Mike Wolfe
' Source    : https://nolongerset.com/generatetvclass/
' Purpose   : Creates a TempVars class module named "TV" based on
'               the contents of a local table.
' ----------------------------------------------------------------
Sub GenerateTVClass(Optional TblName As String = "tblTempVar")
    Dim s As String
    
    'Build the header
    s = s & "' ---===== DO NOT EDIT DIRECTLY =====---" & vbNewLine
    s = s & "' This class module is auto-generated; to recreate run:" & vbNewLine
    s = s & "'     GenerateTVClass " & TblName & vbNewLine & vbNewLine
    s = s & "' For more information see:" & vbNewLine
    s = s & "' - https://nolongerset.com/generatetvclass/" & vbNewLine
    s = s & "' - https://nolongerset.com/tv-class/" & vbNewLine
    s = s & "Option Compare Database" & vbNewLine
    s = s & "Option Explicit" & vbNewLine & vbNewLine
    
    'Build the properties
    With CurrentDb.OpenRecordset(" SELECT * FROM " & TblName & _
                                 " ORDER BY VarName", dbOpenForwardOnly)
        Dim NeedsGetTV As Boolean
        Do Until .EOF
            s = s & vbNewLine
            s = s & "Public Property Let " & !VarName & _
                    "(Value As " & !VarTypeName & "): TempVars(""" & _
                    !VarName & """) = Value: End Property" & vbNewLine
            If IsNull(!VarValue) Then
                s = s & "Public Property Get " & !VarName & _
                        "() As " & !VarTypeName & ": " & !VarName & _
                        " = TempVars(""" & !VarName & """): End Property" & vbNewLine
            Else
                NeedsGetTV = True
                s = s & "Public Property Get " & !VarName & _
                        "() As " & !VarTypeName & ": " & !VarName & _
                        " = GetTV(""" & !VarName & """, """ & !VarValue & _
                        """): End Property" & vbNewLine
            End If
            .MoveNext
        Loop
    End With
    
    'Build the GetTV() function if needed
    If NeedsGetTV Then
        s = s & vbNewLine & vbNewLine & vbNewLine
        s = s & "'By using Variants for the default value and return type, we give up some" & vbNewLine
        s = s & "'   type safety for the convenience of having a single function" & vbNewLine
        s = s & "Private Function GetTV(VarName As String, DefaultValue As Variant) As Variant" & vbNewLine
        s = s & "    Dim Val As Variant" & vbNewLine
        s = s & "    Val = TempVars(VarName)" & vbNewLine
        s = s & "    " & vbNewLine
        s = s & "    If IsNull(Val) Then" & vbNewLine
        s = s & "        GetTV = DefaultValue" & vbNewLine
        s = s & "    Else" & vbNewLine
        s = s & "        GetTV = Val" & vbNewLine
        s = s & "    End If" & vbNewLine
        s = s & "End Function" & vbNewLine
    End If
    
    'Create (or recreate) the class module
    UpsertClassModule "TV", s, True
End Sub

Sample Usage

Once the local table is populated, just run the GenerateTVClass routine from the Immediate Window.  

If all goes well, this will create a class module named TV that looks something like this:

Referenced articles

A Safer Way to Use TempVars

The TempVars object is an intriguing alternative to traditional global variables, but it has some shortcomings. Here’s one way to work around them.

No Longer SetMike Wolfe

TV: The TempVars Class Module

A TempVars class module that provides visibility, type safety, IntelliSense, and compile-time typo checks.

No Longer SetMike Wolfe

Create a Class Module from a String in Microsoft Access

Generate VBA class modules from strings during design time using this simple function in Microsoft Access.

No Longer SetMike Wolfe

VBA Code Generation Trick

Using Notepad++ to transform existing code into a VBA string that generates itself.

No Longer SetMike Wolfe

Image by Sugiarto from Pixabay