Code Library

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.

Mike Wolfe

Oct 17, 2022 • 5 min read

NOTE: This article is about how to create standard code modules from a string in Microsoft Access. I wrote a different article with a function to create class 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 standard code 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.

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

The Approach

The function is quite simple:

Write the contents of the string to a temp file
Load the text file as a 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.

The `UpsertStandardModule` Subroutine

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

Public Sub UpsertStandardModule(ModuleName As String, Contents As String)
    'Write the future module's contents to a temporary file
    Dim fpTemp As String
    fpTemp = WriteTempFile(Contents)
    
    'LoadFromText the contents of the temp file
    LoadFromText acModule, ModuleName, 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

' ----------------------------------------------------------------
' Procedure : UpsertStandardModule
' Date      : 10/17/2022
' Author    : Mike Wolfe
' Source    : https://nolongerset.com/upsertstandardmodule/
' Purpose   : Creates or updates a standard code module.
' Notes     - This routine only runs in Microsoft Access, as it
'               takes advantage of the undocumented Access
'               Application method, LoadFromText.
' ----------------------------------------------------------------
Public Sub UpsertStandardModule(ModuleName As String, Contents As String)
    'Write the future module's contents to a temporary file
    Dim fpTemp As String
    fpTemp = WriteTempFile(Contents)
    
    'LoadFromText the contents of the temp file
    LoadFromText acModule, ModuleName, 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
    'Build a guaranteed-to-be-unique temporary file name
    Dim fpTemp As String
    fpTemp = GetGuidBasedTempPath(FileExt)
    
    'Save the string in the Contents argument to the file
    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
 
    'Return the full path to the temporary file name
    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 & "Sub HelloWorld()" & vbNewLine
    s = s & "    MsgBox ""Hello, world!""" & vbNewLine
    s = s & "End Sub" & vbNewLine
    
    UpsertStandardModule "SampleModule", s
End Sub

Caveat Emptor

I should point out here that the UpsertStandardModule 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

Image by Taco Fleur from Pixabay

UPDATE [2022-10-18]: Added note at top of article with link to complementary function.