MrExcel Publishing
Your One Stop for Excel Tips & Solutions

DELETE MACRO'S / MODULES????


Posted by RONALD on October 09, 2001 4:47 AM

Can anyone tell me how to let Modules or Macro's be deleted by running a macro?

For example Macro1, Macro2 or Module1, Module2.

It's not the meaning that the whole file or folder will be deleted. Only the Macro's or Modules in the file. Hope someone can help me out?

Thanks

Ronald


Posted by Chris on October 09, 2001 6:18 AM

Ronald

Check out this link.

Make sure you update the visual basic references as described at the beginning otherwise the code will not work!

http://www.cpearson.com/excel/vbe.htm

Chris

Posted by Dank on October 09, 2001 6:35 AM

Ronald,

Try this code:-

Sub DeleteAllMacros()
Dim vbComp As VBIDE.VBComponent
Dim wb As Workbook

'This macro will delete all macros in the ACTIVE workbook in Excel.
Set wb = ActiveWorkbook

If wb.Name = ThisWorkbook.Name Then Exit Sub 'Don't want to delete this macro


For Each vbComp In wb.VBProject.VBComponents
Select Case vbComp.Type
Case 1, 2, 3 'Module, class module or form
wb.VBProject.VBComponents.Remove vbComp
Case Else 'ThisWorkbook or some sort of sheet
vbComp.CodeModule.DeleteLines 1, vbComp.CodeModule.CountOfLines
End Select
Next

End Sub


YOU MUST SET A REFERENCE TO THE VBA EXTENSIBILITY LIBRARY! From the VB editor choose Tools, References and tick Visual Basic for Applications Extensibility.

Regards,
Daniel.

Posted by Ronald on October 09, 2001 10:49 PM

Thanks!!!

Both thanks a lot for your help!

Ronald