Deleting Modules & Macros with Code

Posted by Chris on September 03, 2001 1:48 AM

Lets say I have a file with a module called module1 and within that a macro called macro1.

Within the same file I have a separate subroutine in module2 from which I want to delete all of module1.

Does anyone know how to do this?

Help very much appreciated!

Chris

Posted by Dax on September 03, 2001 4:44 AM

Have a look here for full details:-

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

Regards,
Dax.

Posted by Chris on September 03, 2001 4:52 AM

Thanks for the link. Just what I was looking for.

Posted by Chris on September 03, 2001 7:29 AM

I tried the link but Excel 97 doesn't seem to recognise some of the code. The code given is as follows: Does anyone know how to adjust this to Excel 97?

Sub DeleteAllVBA()

Dim VBComp As VBIDE.VBComponent
Dim VBComps As VBIDE.VBComponents

Set VBComps = ActiveWorkbook.VBProject.VBComponents

For Each VBComp In VBComps
Select Case VBComp.Type
Case vbext_ct_stdmodule, vbext_ct_MSForm, vbext_ct_classmodule
VBComps.Remove VBComp
Case Else
With VBComp.CodeModule
.DeleteLines 1, .CountOfLines
End With
End Select
Next VBComp

End Sub

Thanks for the link. Just what I was looking for.

Posted by Dax on September 03, 2001 7:58 AM

Sub DeleteAllVBA() Dim VBComp As VBIDE.VBComponent

The code will work fine with Excel 97 - I just tried it. You need to ensure that you've set a reference to correct library. In the VB editor click Tools, Reference and select Microsoft Visual Basic for Applications Extensibility.

Regards,
Dax.

Posted by Chris on September 03, 2001 8:09 AM

You're a Star! thanks Dax