Add a procedure to a module
2000-02-05 VBE 2 196
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