Add a procedure to a module using VBA in Microsoft Excel

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"

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube