PDA

View Full Version : Solved: How to run a macro to delete other macro?



fanjy
09-09-2006, 04:44 AM
Hello!everyone.I have a question.When I run a macro,I want to delete the other macro.But a error will happen with "out of subscript".The code is:

Sub macro1()
MsgBox "hello!"
End Sub
Sub macro2()
With ActiveWorkbook.VBProject
.VBComponents.Remove .VBComponents("macro1")
End With
End Sub

thanks to any assistance.

Bob Phillips
09-09-2006, 05:05 AM
This is how to do it



'----------------------------------------------------------------
Sub DeleteProcedure()
'----------------------------------------------------------------
Dim oCodeModule As Object
Dim iStart As Long
Dim cLines As Long

Set oCodeModule = ThisWorkbook.VBProject.VBComponents("Module1").CodeModule
With oCodeModule
On Error GoTo dp_err:
iStart = .ProcStartLine("myProc", 0)
cLines = .ProcCountLines("myProc", 0)
.DeleteLines iStart, cLines
On Error GoTo 0
Exit Sub
End With

dp_err:
If Err.Number = 35 Then
MsgBox "Procedure does not exist"
End If
End Sub

fanjy
09-09-2006, 05:42 AM
xld.Now my macro can run.Thank you very much.