» Delete a procedure from 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 With the macro below you can delete an existing procedure from a module:

Sub DeleteProcedureCode(ByVal wb As Workbook, _
    ByVal DeleteFromModuleName As String, ByVal ProcedureName As String)
' deletes ProcedureName from DeleteFromModuleName in wb
Dim VBCM As CodeModule, ProcStartLine As Long, ProcLineCount As Long
    On Error Resume Next
    Set VBCM = wb.VBProject.VBComponents(DeleteFromModuleName).CodeModule
    If Not VBCM Is Nothing Then
        ' determine if the procedure exist in the codemodule
        ProcStartLine = 0
        ProcStartLine = VBCM.ProcStartLine(ProcedureName, vbext_pk_Proc)
        If ProcStartLine > 0 Then ' prosedyren finnes, slett den
            ProcLineCount = VBCM.ProcCountLines(ProcedureName, vbext_pk_Proc)
            VBCM.DeleteLines ProcStartLine, ProcLineCount
        End If
        Set VBCM = Nothing
    End If
    On Error GoTo 0
End Sub

Example:

DeleteProcedureCode Workbooks("WorkBookName.xls"), _
    "Module1", "ProcedureName"