Add a procedure to a module

 2000-02-05    VBE    2    163

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 AddProcedureCode(wb As Workbook, strModuleName As String)
' requires a reference to the Microsoft Visual Basic 5.0/6.0 Extensibility library (or later)
' you must also allow macros to access the VBA project model (see Excel Trust Center, Macro Settings)
    On Error Resume Next
    With wb.VBProject.VBComponents(strModuleName).CodeModule
        .InsertLines .CountOfLines + 1, "Sub NewSubName()"
        .InsertLines .CountOfLines + 1, "   Msgbox ""Hello World!"",vbInformation,""Message Box Title"""
        .InsertLines .CountOfLines + 1, "End Sub" & Chr(13)
    End With
    On Error GoTo 0
End Sub

Sub TestAddProcedureCode()
    AddProcedureCode ThisWorkbook, "Module1"
    AddProcedureCode ThisWorkbook, Worksheets("Sheet1").CodeModule
End Sub