[VBA] How to Automatically Generate and Edit VBA Code (CodeModule Object)

2025年12月27日

Introduction

Using the VBProject object in VBA allows you to export and import modules. However, going a step further, you can read and write the code inside a module line by line using a macro.

This is achieved using the CodeModule object. Its methods allow you to automate operations such as adding, inserting, replacing, and deleting code.

In this article, I will explain this highly advanced technique of programmatically editing VBA code using the main methods of the CodeModule object.

Important: Preparation

To run the macros below, you must enable “Trust access to the VBA project object model” in Excel’s Trust Center settings.

Main Methods of the CodeModule Object

In these examples, we will manipulate a standard module named “TargetModule”.

1. Adding Code as a String (.AddFromString)

This method adds a specified string as new code lines to the end of the module.

' Add code to the end of the module
Sub AddCodeToModule()
    Dim codeToAdd As String
    codeToAdd = vbCrLf & "' --- This code was added by a macro ---"
    
    ' Get the CodeModule object of TargetModule and add code
    ThisWorkbook.VBProject.VBComponents("TargetModule").CodeModule.AddFromString codeToAdd
    
    MsgBox "Code added."
End Sub

2. Inserting Code at a Specific Line (.InsertLines)

This method inserts new code lines at a specified line number. Existing code shifts down.

' Insert code into line 2 of the module
Sub InsertCodeIntoModule()
    Dim codeToInsert As String
    codeToInsert = "' Updated on " & Date
    
    ' Insert string at line 2
    ThisWorkbook.VBProject.VBComponents("TargetModule").CodeModule.InsertLines 2, codeToInsert
    
    MsgBox "Code inserted at line 2."
End Sub

3. Replacing Code at a Specific Line (.ReplaceLine)

This method completely replaces the code at a specified line number with a new string.

' Replace line 3 of the module
Sub ReplaceLineInModule()
    Dim codeToReplace As String
    codeToReplace = "    ' This line was replaced by a macro"
    
    ' Replace the content of line 3
    ThisWorkbook.VBProject.VBComponents("TargetModule").CodeModule.ReplaceLine 3, codeToReplace
    
    MsgBox "Line 3 replaced."
End Sub

4. Deleting Specific Lines (.DeleteLines)

This method deletes a specified number of lines starting from a specified line number.

' Delete 2 lines starting from line 5 of the module
Sub DeleteLinesFromModule()
    ' Delete 2 lines starting from line 5
    ThisWorkbook.VBProject.VBComponents("TargetModule").CodeModule.DeleteLines 5, 2
    
    MsgBox "Deleted 2 lines starting from line 5."
End Sub

Summary

In this article, I explained an advanced meta-programming technique to edit VBA code itself using the CodeModule object.

.AddFromString: Add code to the end of the module.
.InsertLines: Insert code at a specific line.
.ReplaceLine: Replace code at a specific line.
.DeleteLines: Delete code starting from a specific line.

By combining these methods, you can create powerful development support macros, such as tools that automatically generate standard macro templates or refactoring tools that batch update old code.

よかったらシェアしてね！