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:

Build the header
Build the Let/Get properties from the source table
If needed, build a private function to support TempVars with default values
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