MrExcel Publishing
Your One Stop for Excel Tips & Solutions

removing macros when finished

Posted by Craig on November 21, 2001 12:14 PM

Is there a way (using macros, not menus) to remove one or more macros from a spreadsheet? I have a spreadsheet that I use as a basis for a regular report. I update some data in it and run a macro to do some major manipulation of the data into a report then finally saving the resulting report with the date as the filename. I do not want the macro in the final report so that I can remove the possibility of the data in a finished report being accidentally processed more than once. All I have been able to come up with is this at the end of the macro itself;

Lns = Application.VBE.ActiveVBProject.VBComponents("Module1").CodeModule.CountOfLines
Application.VBE.ActiveVBProject.VBComponents("Module1").CodeModule.DeleteLines 1, Lns

Any other suggestions?

Posted by Ivan F Moala on November 21, 2001 7:00 PM

One way to do this is;

In std module

Sub DeleteAllModules_ExceptThisone()
Dim x As Variant
For Each x In ActiveWorkbook.VBProject.VBComponents
If x.Type = 1 Then
If x.Name <> "Mod_Ctrl" Then
ActiveWorkbook.VBProject.VBComponents.Remove x
End If
End If
End Sub

Name this module "Mod_Ctrl" to make sure you do not inadvertently delete it.
NB: you need MS extensibility.

Just before saving it run the above macro.
It will delete ALL modules EXCEPT this one that
you have named Mod_Ctrl.



Posted by Craig on November 23, 2001 5:58 AM

Excellent! Thank you Ivan.

This is exactly what I was looking for.