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
Next
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.
Ivan
Ivan
Posted by Craig on November 23, 2001 5:58 AM
Excellent! Thank you Ivan.
This is exactly what I was looking for.
