Remove All VBA

[Otto Moehrbach]

Excel 2007 Win 7 64-bit
I have a wb with VBA code that does various things. The code then savesAS
the wb in another name. At that point, I want the same code to remove all
code from that wb and save the wb. Can that be done and how? Thanks for
your time. Otto

 

[Gord Dibben]

See Chip's site for programmatically removing VBA from a workbook.

http://www.cpearson.com/excel/VBE.aspx


Gord Dibben MS Excel MVP

 

[Mike H]

Hi,

This would after the SaveAs routine

Sub DeleteCode()
Msg = "Are you sure you want to delete Code from " & ActiveWorkbook.Name
Response = MsgBox(Msg, vbYesNo)
If Response = vbNo Then Exit Sub
On Error Resume Next
With ActiveWorkbook.VBProject
For X = .VBComponents.Count To 1 Step -1
.VBComponents.Remove .VBComponents(X)
Next X
For X = .VBComponents.Count To 1 Step -1
.VBComponents(X).CodeModule.DeleteLines _
1, .VBComponents(X).CodeModule.CountOfLines
Next X
End With
End Sub

Mike

 

[Ron de Bruin]

Save the file as xlsx and all your code is gone

Maybe this page will help
http://www.rondebruin.nl/saveas.htm

 

[Mike H]

The learning curve for me with E2007 shows no sign of becoming less steep,
thanks for that tip.

Mike

 

[Otto Moehrbach]

Mike
Ditto. I'm old and there is no end to it. Otto

 

[Otto Moehrbach]

Ron
I did that and the file now is an xlsx file. All the code is there and
it runs!! Did I miss something? Each time I save it, it warns me that I
will lose all my code. I click Yes and the code is still there. Otto

 

[Otto Moehrbach]

Ron
After saving it as an xlsx file, I closed the file. Closed Excel.
Opened Excel and the file and the code is gone. Otto

 

[Gord Dibben]

Thanks for posting this Otto.

Like you. I never thought of closing Excel then re-opening after saving as
*.xlsx


Gord