Add a procedure to a module

 2000-02-05    VBE    2    67

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


Leave a comment:

Your comment will only be published after it has been moderated and found spam free.
Your e-mail address will only be used to display your Gravatar.

OPE | 2012-08-28 16:42:50 (GMT)

Things has evolved since year 2000 when this was originally posted, so the Microsoft Visual Basic for Applications Extensibility 5.3 library should be the latest library available now.
You must also remember to allow macros to access the VBA project model, you find this option in the Excel Trust Center, Macro Settings.

Jørgen Rasmussen | 2012-08-24 13:25:45 (GMT)

I find the code here very interesting and useful.

But for some reason I do have problems activating it.

You write that to run the code it requires a reference to the Microsoft Visual Basic 5.0/6.0 Extensibility library

But does the Microsoft Visual Basic for applications Extensibility 5.3 not solve the same need?

In that case that might be my problem.

best regard

Jørgen