Delete a procedure from a module using VBA in Microsoft Excel

In this article, we will create a macro to delete another macro from a module.

We are using Module1, which contains SampleProcedure as the sample macro, which we want to delete.

Code explanation

Set VBCM = WB.VBProject.VBComponents(DeleteFromModuleName).CodeModule

The above code is used to create an object of the defined module.

ProcStartLine = VBCM.ProcStartLine(ProcedureName, vbext_pk_Proc)

The above code is used to get the starting line number of the defined procedure.

ProcLineCount = VBCM.ProcCountLines(ProcedureName, vbext_pk_Proc)

The above code is used to get the count of number of lines in the defined procedure.

VBCM.DeleteLines ProcStartLine, ProcLineCount

The above code is used to delete all the lines within the defined procedure.

Please follow below for the code

Option Explicit

Sub DeleteProcedureCode(ByVal DeleteFromModuleName As String, ByVal ProcedureName As String)

'Declaring variables
Dim VBCM As CodeModule, ProcStartLine As Long, ProcLineCount As Long
Dim WB As Workbook

On Error Resume Next

'Creating object of active workbook
Set WB = ActiveWorkbook

'Creating object of workbook module
Set VBCM = WB.VBProject.VBComponents(DeleteFromModuleName).CodeModule

'Checking whether the procedure exist in the codemodule
If Not VBCM Is Nothing Then
    
    ProcStartLine = 0
    
    'Function assigning the line no. of starting line for the procedure
    ProcStartLine = VBCM.ProcStartLine(ProcedureName, vbext_pk_Proc)
    
    If ProcStartLine > 0 Then
        
        'Function assign the no. of lines in the procedure
        ProcLineCount = VBCM.ProcCountLines(ProcedureName, vbext_pk_Proc)
        
        'Delete all the lines in the procedure
        VBCM.DeleteLines ProcStartLine, ProcLineCount
    
    End If
    Set VBCM = Nothing
End If

On Error GoTo 0

End Sub

Sub CallingProcedure()

'Declaring variables
Dim ModuleName, ProcedureName As String

'Getting value for module and procedure name from textboxes
ModuleName = Sheet1.TextBox1.Value
ProcedureName = Sheet1.TextBox2.Value

'Calling DeleteProcedureCode macro
DeleteProcedureCode ModuleName, ProcedureName
    
End Sub

If you liked this blog, share it with your friends on Facebook. Also, you can follow us on Twitter and Facebook.

We would love to hear from you, do let us know how we can improve our work and make it better for you. Write to us at info@exceltip.com