Writing Code with Code in VBA

In VBA, it's possible to write code that adds, deletes, or modifies other code, even within the same project.  This is possible via the VBA Extensibility library.  Chip Pearson has a great introductory article on the topic: Programming the VBA Editor.  Chip's site is focused on VBA within the context of Excel, but the concept is the same.

Words of warning

Generating code within the same project from which you are executing your code is dangerous and should be approached with caution.  Several things can go wrong:

• Access could hard crash resulting in the loss of any unsaved work
• Your Access application could get irrecoverably corrupted
• You could accidentally delete or modify large portions of your code

Mitigating the risk

By following these recommendations, you can greatly reduce your risk from the above threats:

• Keep your application under version control (you should be doing this anyway)
• Save your project immediately before running any code like of this kind
• Backup the .mdb/.accdb file after saving but before running the code

Why do this at all?

There are a variety of reasons you might want to do this sort of thing:

• Code generation of ORM-type classes
• Doc testing private routines and class modules
• Automating the creation of boilerplate-heavy code

My contribution

The WriteModule procedure below creates or updates a Standard or Class Module named ModName.  The Contents parameter should contain the lines of code that will comprise the module.  ModuleType is a string instead of the vbext_ComponentType enum type.  That allows us to use late-binding of the VBIDE library and avoid adding the "Microsoft Visual Basic for Applications Extensibility 5.3" reference to our application.

WARNING: As noted in the comments above the procedure, this routine will OVERWRITE the contents of an existing code module.  I recommend you run this with a test module before changing any of your existing modules.

'Produces a code module named {ModName} with the Contents passed
'   - If the module exists, it is overwritten
'   - If the module does not exist, it is created and populated
'   - Contents should include *all* needed code, e.g. "Option Explicit"
Sub WriteModule(ModName As String, Contents As String, ModuleType As String)
    On Error GoTo Err_WriteModule

    Dim VBProj As Object ' VBIDE.VBProject
    Dim VBComp As Object ' VBIDE.VBComponent
        
    'Get the code module object
    Set VBProj = VBE.ActiveVBProject
    Set VBComp = VBProj.VBComponents(ModName)
    
    'Delete the contents of the module
    VBComp.CodeModule.DeleteLines 1, VBComp.CodeModule.CountOfLines
    
    'Update the contents of the module
    VBComp.CodeModule.AddFromString Contents


Exit_WriteModule:
    Exit Sub
Err_WriteModule:
    Select Case Err.Number
    Case 9  'Subscript out of range
        Select Case ModuleType
        Case "Standard"
            Set VBComp = VBProj.VBComponents.Add(1)  'vbext_ct_StdModule
        Case "Class"
            Set VBComp = VBProj.VBComponents.Add(2)  'vbext_ct_ClassModule
        Case Else
            'https://nolongerset.com/throwing-errors-in-vba/
            Throw "Unsupported ModuleType: {0}", ModuleType
        End Select
        VBComp.Name = ModName
        Resume
    Case Else
        LogErr Err, Errors, "DesignProcedures", "WriteModule"
    End Select
    Resume Exit_WriteModule
End Sub

Image by WikiImages from Pixabay