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
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.