Removing all code from a Project

[Stuart]

Chip Pearson gives the following code on his website to
remove all VBA code in a Project:

Private 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

For this to work, I have set a reference to MS Visual Basic
For Applications Extensibility 5.3. I'm currently using this
with Excel 2000 under Win 2k.

This code is in the ThisWorkbook module, and runs
under a Workbook Before_Save event where a test is
made to determine if the user's work is done. If so, then
the 'strip VBA' code runs.and then saves the workbook
.....except it doesn't run properly.

I get the following mixture of results:
1. It works
2. It closes the workbook without errors but does not
delete the code
3. It closes the workbook, then closes excel.
4. I get this error:
Compile error: expected End Property
If I Ok that message, it asks if I want to save changes
to the workbook, so I Cancel. Module1 has been
removed and the ThisWorkbook module is empty.

Can anyone point to my errors, please?
Regards.

 

[Stuart]

May have found my error.

When the test to strip code is met, I 'call' the routine
(ie I had placed it in another sub).
Have now placed it 'line for line' in the Before_Save
event code, and it seems to work ....although when
the newly-saved book is reopened, then the macro
warning message is displayed.

Not sure if this has solved everything. If so, can I
avoid that warning message?

Regards.

 

[Bob Phillips]

Haven't tested it, but is the code that is doing the deleting getting
deleted before it finishes?

Try testing for the ThisWorkbook code module, and bypassing that delete, and
then specifically delete that at the end.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Stuart]

Many thanks.
I think my follow-up post may indicate that I
was 'jumping' between modules, which doesn't
seem very logical when I think about it <g>.
Regards.

 

[Bob Phillips]

Actually, as soon as I posted it I knew it was a daft answer, as the code
will load into memory at the start, and the deletes will be to the physical
workbook, so it won't impact at all.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Stuart]

Doesn't seem daft at all ... least not when compared
to how I was trying to run the code.

Maybe I've fixed it. We'll see.

Regards.