» Add a procedure to a module using VBA in Microsoft Excel

VBA macro tip contributed by Erlandsen Data Consulting offering Microsoft Excel Application development, template customization, support and training solutions You can add code to a module without using a separate text file that contains the code.
The macro below shows how this can be done. The macro must be customized to contain to the code you want to add:

Sub InsertProcedureCode(ByVal wb As Workbook, ByVal InsertToModuleName As String)
' inserts new code in module named InsertModuleName in wb
' needs customizing depending on the code to insert
Dim VBCM As CodeModule
Dim InsertLineIndex As Long
    On Error Resume Next
    Set VBCM = wb.VBProject.VBComponents(InsertToModuleName).CodeModule
    If Not VBCM Is Nothing Then
        With VBCM
            InsertLineIndex = .CountOfLines + 1
            ' customize the next lines depending on the code you want to insert
            .InsertLines InsertLineIndex, "Sub NewSubName()" & Chr(13)
            InsertLineIndex = InsertLineIndex + 1
            .InsertLines InsertLineIndex, _
                "    Msgbox ""Hello World!"",vbInformation,""Message Box Title""" & Chr(13)
            InsertLineIndex = InsertLineIndex + 1
            .InsertLines InsertLineIndex, "End Sub" & Chr(13)
            ' no need for more customizing
        End With
        Set VBCM = Nothing
    End If
    On Error GoTo 0
End Sub

Example:

InsertProcedureCode Workbooks("WorkBookName.xls"), "Module1"