Create a Class Module from a String in Microsoft Access

NOTE: This article is about how to create class modules from strings in Microsoft Access. I wrote a different article with a function to create standard modules.

Writing boilerplate code is a necessary evil in VBA. And writing it by hand has some real drawbacks:

It is tedious
It creates more opportunities for bugs
It is prone to silly mistakes
Refactoring the underlying logic must be done in multiple places

One way to avoid these downsides–especially the last one–is to automate the generation of the boilerplate code.

Starting Point

For the purposes of this article, we will assume that you already have a string with the contents of a VBA class module.

How you build that string will vary widely from situation to situation. One technique that may help with the string-building portion is my Notepad++ VBA code generation trick.

VBA Code Generation Trick

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

No Longer SetMike Wolfe

Once you have your string, the UpsertClassModule procedure below will create or overwrite a standard code module with its contents.

The Approach

The function is quite simple:

Prefix the class module contents with the hidden attributes of a class module
Write the contents of the combined string to a temp file
Load the text file as a class module
Delete the temp file

For simplicity, we take advantage of the Access Application object's undocumented LoadFromText function. This lets us avoid having to write code against the VBA Extensibility library. The downside is that it only runs in Microsoft Access. One could write a more general VBA function relatively easily, but it would be (much) more verbose.

Hidden Class Module Attributes

Unlike standard modules, VBA class modules contain four hidden attributes that are not visible in the VBA IDE:

VB_GlobalNamespace
VB_Creatable
VB_PredeclaredId
VB_Exposed

You can read about these attributes here and here.

If you create a new class module in an Access application, save it with a default name of Class1.txt, and then use the SaveAsText method to export it to a text file, you can see these attributes (all set to their default values of False):

Every VBA class module includes at least four hidden attributes that cannot be viewed or changed from within the VBA IDE.

The `UpsertClassModule` Subroutine

Here is the subroutine on its own without its required dependencies:

Public Sub UpsertClassModule(ClassName As String, Contents As String, _
    Optional PredeclaredId As Boolean = False, _
    Optional Creatable As Boolean = False, _
    Optional Exposed As Boolean = False, _
    Optional GlobalNameSpace As Boolean = False)
    
    'Build hidden attribute string to prepend to the class module's contents;
    '   we use IIf() to return a literal "True" or "False" value so that
    '   the code works properly when the system language is not set to English
    Dim HiddenAttributes As String
    HiddenAttributes = _
        "Attribute VB_GlobalNameSpace = " & IIf(GlobalNameSpace, "True", "False") & vbNewLine & _
        "Attribute VB_Creatable = " & IIf(Creatable, "True", "False") & vbNewLine & _
        "Attribute VB_PredeclaredId = " & IIf(PredeclaredId, "True", "False") & vbNewLine & _
        "Attribute VB_Exposed = " & IIf(Exposed, "True", "False") & vbNewLine
    
    'Write the future class module's contents to a temporary file
    Dim fpTemp As String
    fpTemp = WriteTempFile(HiddenAttributes & Contents)
    
    'LoadFromText the contents of the temp file
    LoadFromText acModule, ClassName, fpTemp
    
    'Delete the temp file
    Kill fpTemp
End Sub

The Full Code

The code below includes required helper functions that I've written about in the past:

The code can be copied and pasted into a blank standard module to get a fully-working solution that you can easily integrate into your projects:

Option Compare Database
Option Explicit

'--== Declare lines for CreateGUID() ==--
#If VBA7 Then
    Private Declare PtrSafe Function CoCreateGuid Lib "ole32" (id As Any) As Long
#Else
    Private Declare Function CoCreateGuid Lib "ole32" (id As Any) As Long
#End If

'--== Declare lines for DeleteFile() ==--
#If VBA7 Then
    Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#Else
    Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#End If

' ----------------------------------------------------------------
' Procedure : UpsertClassModule
' Date      : 10/18/2022
' Author    : Mike Wolfe
' Source    : https://nolongerset.com/upsertclassmodule/
' Purpose   : Creates or updates a class module.
' Notes     - This routine only runs in Microsoft Access, as it
'               takes advantage of the undocumented Access
'               Application method, LoadFromText.
' ----------------------------------------------------------------
Public Sub UpsertClassModule(ClassName As String, Contents As String, _
    Optional PredeclaredId As Boolean = False, _
    Optional Creatable As Boolean = False, _
    Optional Exposed As Boolean = False, _
    Optional GlobalNameSpace As Boolean = False)
    
    'Build hidden attribute string to prepend to the class module's contents;
    '   we use IIf() to return a literal "True" or "False" value so that
    '   the code works properly when the system language is not set to English
    Dim HiddenAttributes As String
    HiddenAttributes = _
        "Attribute VB_GlobalNameSpace = " & IIf(GlobalNameSpace, "True", "False") & vbNewLine & _
        "Attribute VB_Creatable = " & IIf(Creatable, "True", "False") & vbNewLine & _
        "Attribute VB_PredeclaredId = " & IIf(PredeclaredId, "True", "False") & vbNewLine & _
        "Attribute VB_Exposed = " & IIf(Exposed, "True", "False") & vbNewLine
    
    'Write the future class module's contents to a temporary file
    Dim fpTemp As String
    fpTemp = WriteTempFile(HiddenAttributes & Contents)
    
    'LoadFromText the contents of the temp file
    LoadFromText acModule, ClassName, fpTemp
    
    'Delete the temp file
    Kill fpTemp
End Sub


' ----------------------------------------------------------------
' Procedure : WriteTempFile
' Date      : 10/11/2022
' Author    : Mike Wolfe
' Source    : https://nolongerset.com/writetempfile/
' Purpose   : Save the passed Contents to a GUID-based temporary file.
' Returns   : The full path to the temporary file.
' Notes     - Since the file name is based on a GUID, we don't
'               need to check if the file already exists.
' ----------------------------------------------------------------
Private Function WriteTempFile(Contents As String, Optional FileExt As String = "txt") As String
    Dim fpTemp As String: fpTemp = GetGuidBasedTempPath(FileExt)
    
    Dim FNum As Integer
    FNum = FreeFile()
    Open fpTemp For Output As FNum
    'trailing semi-colon needed to prevent adding blank line at end of file
    '  see: http://stackoverflow.com/a/9445141/154439
    Print #FNum, Contents;
    Close #FNum
 
    WriteTempFile = fpTemp
End Function

' ----------------------------------------------------------------
' Procedure : GetGuidBasedTempPath
' Date      : 9/30/2022
' Author    : Mike Wolfe
' Source    : https://nolongerset.com/getguidbasedtemppath/
' Purpose   : Returns an unused full path in the user's temp folder.
' Requires  : CreateGUID() from https://nolongerset.com/createguid/
' Notes     - For efficiency, the file name is based on a GUID which
'               obviates the need to check for an existing file.
' Usage     : ?GetGuidBasedTempPath()  'produces something like:
'   C:\Users\Mike\AppData\Local\Temp\11132708-F988-E84A-ADB0-F27F133B2116.tmp
' ----------------------------------------------------------------
Private Function GetGuidBasedTempPath(Optional FileExt As String = "tmp")
    'Build the destination folder with trailing slash
    Dim foDest As String
    foDest = Environ("TMP")
    If Right(foDest, 1) <> "\" Then foDest = foDest & "\"
    
    'Build the destination filename with extension
    Dim fnDest As String
    fnDest = CreateGUID() & "." & FileExt
    
    'Combine the folder and filename to create the full path
    GetGuidBasedTempPath = foDest & fnDest
End Function


' ----------------------------------------------------------------
' Procedure  : CreateGUID
' Author     : Dan (webmaster@1stchoiceav.com)
' Source     : http://allapi.mentalis.org/apilist/CDB74B0DFA5C75B7C6AFE60D3295A96F.html
' Adapted by : Mike Wolfe
' Republished: https://nolongerset.com/createguid/
' Date       : 8/5/2022
' ----------------------------------------------------------------
Private Function CreateGUID() As String
    Const S_OK As Long = 0
    Dim id(0 To 15) As Byte
    Dim Cnt As Long, GUID As String
    If CoCreateGuid(id(0)) = S_OK Then
        For Cnt = 0 To 15
            CreateGUID = CreateGUID & IIf(id(Cnt) < 16, "0", "") + Hex$(id(Cnt))
        Next Cnt
        CreateGUID = Left$(CreateGUID, 8) & "-" & _
                     Mid$(CreateGUID, 9, 4) & "-" & _
                     Mid$(CreateGUID, 13, 4) & "-" & _
                     Mid$(CreateGUID, 17, 4) & "-" & _
                     Right$(CreateGUID, 12)
    Else
        MsgBox "Error while creating GUID!"
    End If
End Function

Sample Usage

Sub SampleUsage()
    Dim s As String

    s = s & "Option Explicit" & vbNewLine
    s = s & "Option Compare Database" & vbNewLine
    s = s & vbNewLine
    s = s & "Property Get TheAnswer() As Integer" & vbNewLine
    s = s & "    TheAnswer = 42" & vbNewLine
    s = s & "End Property" & vbNewLine
    
    UpsertClassModule "Guide", s, True
End Sub

Caveat Emptor

I should point out here that the UpsertClassModule code is intended to be used at DESIGN TIME by you, the developer. It is not intended to generate code on the fly at run time. It might work. In fact, it probably will work–especially on your development machine. But it might also trigger the heuristic analysis scanners for some of your users with modern anti-virus software, thus blocking your application from running at all.

Referenced Articles

Writing Boilerplate Code by Hand is for Suckers

Writing repetitive code may be a necessary evil in VBA, but it’s a lot less tedious if you generate most of it in VBA itself.

No Longer SetMike Wolfe

External References

[MS-VBAL]: Default Instance Variables Static Semantics

A class module has a default instance variable if its VB_PredeclaredId attribute or VB_GlobalNamespace attribute has the value True . This

Microsoft Learnopenspecs-office

[MS-VBAL]: Class Accessibility and Instancing

The ability to reference a class by its name is determined by the accessibility of its class definition. This accessibility is distinct from the