PDA

View Full Version : Macro to delete other macro or Module



SAhak
03-07-2007, 09:52 AM
Hi All,

I tried to use macros posted before in forums but no success. I have Excel 2002. I would like macro which will delete an other macro (or module).

Sahak

Simon Lloyd
03-07-2007, 10:10 AM
Hi, Chip Pearson's site has well explained and documented examples on this http://www.cpearson.com/excel/vbe.htm

Regards,
Simon

Bob Phillips
03-07-2007, 11:59 AM
Delete a procedure



Const vbext_pk_Proc = 0

'----------------------------------------------------------------
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", vbext_pk_Proc)
cLines = .ProcCountLines("myProc", vbext_pk_Proc)
.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


delete a module



Const vbext_pk_Proc = 0

'standard, form or class module

'----------------------------------------------------------------
Function DeleteModule(moduleName As String)
'----------------------------------------------------------------
Dim vbMod As Object

Set vbMod = ActiveWorkbook.VBProject.VBComponents(moduleName)
ThisWorkbook.VBProject.VBComponents.Remove vbMod

End Function

SAhak
03-07-2007, 02:20 PM
Hi Simon, Xld,

Thank you very much for your reply, appreciated.

For XLD
I run macro DeleteProcedure. Yes It Works. It worked after I have checked CheckBox ?Trust access to Visual Basic Project? (Tools > Macros > Security > Trusted Sources). Is it possible with using a macro to have CheckBox?s value = true ?

About Function DeleteModule(moduleName AsString). How I should run or call this Function to be worked?

Thanks again in advance.

Sahak

Bob Phillips
03-07-2007, 02:26 PM
No.

Call DeleteModule("myModule")

SAhak
03-08-2007, 08:33 AM
Thank you very much

Robert
04-12-2007, 09:25 AM
Will this work if I have many commandbutton on several worksheets?

lucas
04-12-2007, 09:28 AM
It won't delete the buttons....but the selected code modules will be deleted.