> ## Content Index
> Fetch the complete content index at: https://nolongerset.com/llms.txt
> Use this file to discover other available public pages before exploring further.

# Writing Boilerplate Code by Hand is for Suckers
- URL: https://nolongerset.com/upsertstandardmodule/
- Published: 2022-10-18T01:47:44.000Z
- Updated: 2026-05-08T12:54:09.000Z
- Description: 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.
- Author: Mike Wolfe
- Tags: Code Library, #CodeGeneration, #Import 2026-05-20 02:59

*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](https://nolongerset.com/upsertclassmodule/).*‌

---

Writing boilerplate code is a [necessary evil in VBA](https://nolongerset.com/boilerplate-code-problems/). 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](https://nolongerset.com/vba-code-generation-trick/).

[VBA Code Generation TrickUsing Notepad++ to transform existing code into a VBA string that generates itself.![](https://nolongerset.com/favicon.png)No Longer SetMike Wolfe![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2020/12/Generating-VBA-Strings-with-Notepad---Macros.jpg)](https://nolongerset.com/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:

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

```vba
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:

- [WriteTempFile(): A Simple VBA Function to Save Text to a New Temporary File](https://nolongerset.com/writetempfile/)
- [CreateGuid: A Reliable Way to Generate GUIDs in VBA](https://nolongerset.com/createguid/)
- [A GUID-Based Temporary File Name Generator](https://nolongerset.com/getguidbasedtemppath/)

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:

```vba
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

```vba
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
```

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2022/10/image-40.png)

#### *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](https://en.wikipedia.org/wiki/Heuristic%5Fanalysis) scanners for some of your users with modern anti-virus software, thus blocking your application from running at all.

---

### Referenced Articles

[Create a Class Module from a String in Microsoft AccessGenerate VBA class modules from strings during design time using this simple function in Microsoft Access.![](https://nolongerset.com/favicon.png)No Longer SetMike Wolfe![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2022/10/portrait-5544201_1920.jpg)](https://nolongerset.com/upsertclassmodule/)

*Image by [Taco Fleur](https://pixabay.com/users/tacofleur-2688180/?utm%5Fsource=link-attribution&utm%5Fmedium=referral&utm%5Fcampaign=image&utm%5Fcontent=2141197) from [Pixabay](https://pixabay.com//?utm%5Fsource=link-attribution&utm%5Fmedium=referral&utm%5Fcampaign=image&utm%5Fcontent=2141197)*

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