Sir Chip,

Thank you for the rapid response. Regreatfully, when I
visit the URL you provided all I get is a nice header
and...

<table border="0" wid

I'll try again later.
Again, may I extend my appreciation for your support?

Earle, Data Troll


-----Original Message-----
Earle,

See the "Deleting All VBA Code In A Project" section at
http://www.cpearson.com/excel/vbe.htm .


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Earle" wrote in message
...
Greetings,

Does anyone have macro/VBA code that will find all
Macros
and all user-added Functions, and delete them enmasse?

This code does pass the Compile test, but always returns
Zero as Help says the various properties/methods
of .Modules is hidden and not available:
Dim intVBA As Integer
intVBA = ActiveWorkbook.Modules.Count

I find it a royal pain in the mouse to have to delete
Macros and user-Functions one at a time. These are all
preparation/formatting/processing macros/functions and
are
not needed once the workbook is in the hands of users.

And it would be really neat if the KillMacros macro
would
delete itself as the last thing it does.

Regards to All


.

Hi Earle.

Here's the code that Chip references:

Deleting All VBA Code In A Project

The procedure below will delete all the VBA code in a project. You should
use this procedure with care, as it will permanently delete the code.
Standard modules, user forms, and class modules will be removed, and code
within the ThisWorkbook module and the sheet modules will be deleted. You
may want to export the VBA code, using the procedure above, before deleting
the VBA code.

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

Sincerely,

Rita Nikas, MCSE MCDBA
Product Support Services
Microsoft Corporation

This posting is provided "AS IS" with no warranties, and confers no rights.


--------------------
| Subject: Excel: Deleting Macros and Functions
| Date: Tue, 8 Jul 2003 06:31:19 -0700
|
| Sir Chip,
|
| Thank you for the rapid response. Regreatfully, when I
| visit the URL you provided all I get is a nice header
| and...
|
| <table border="0" wid
|
| I'll try again later.
| Again, may I extend my appreciation for your support?
|
| Earle, Data Troll
|
|
| -----Original Message-----
| Earle,
|
| See the "Deleting All VBA Code In A Project" section at
| http://www.cpearson.com/excel/vbe.htm .
|
|
| --
| Cordially,
| Chip Pearson
| Microsoft MVP - Excel
| Pearson Software Consulting, LLC
| www.cpearson.com
|
|
|
| "Earle" wrote in message
| ...
| Greetings,
|
| Does anyone have macro/VBA code that will find all
| Macros
| and all user-added Functions, and delete them enmasse?
|
| This code does pass the Compile test, but always returns
| Zero as Help says the various properties/methods
| of .Modules is hidden and not available:
| Dim intVBA As Integer
| intVBA = ActiveWorkbook.Modules.Count
|
| I find it a royal pain in the mouse to have to delete
| Macros and user-Functions one at a time. These are all
| preparation/formatting/processing macros/functions and
| are
| not needed once the workbook is in the hands of users.
|
| And it would be really neat if the KillMacros macro
| would
| delete itself as the last thing it does.
|
| Regards to All
|
|
| .
|
|