Hi Rob,

You need to set a reference the visual Basic Extensibility library:

VBA Menu | Tools | References


---
Regards,
Norman



"Rob" wrote in message
...
I have a workbook that contains several macros that manipulate and
calculate
data for billing to be sent to a client. After all calculations are
performed, I will be copying a pasting the worksheets to themselves as
values
only so that the client will not be able to see the formulas. Afterwards,
I
would like to delete all the macros as well. I found the following code
through the discussion group:

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


However, when I try to run this macro, I get an error message at the very
first line that says:

Compile Error:

User defined type not defined

Obviously I would like to know what to change and how to delete all the
macros, including the one that is being written to delete the macros.

Can anyone help?

Hi Rob,

For additional information, see Chip Pearson's Programming To The Visual
Basic Editor page at:


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

---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Rob,

You need to set a reference the visual Basic Extensibility library:

VBA Menu | Tools | References


---
Regards,
Norman

Hi Rob,

'------------------
One problem, I am going to be saving this workbook with no macros to
different file name. So the next month, I would open the original file with
the macros and run those to perform the calculations. When I would finish
with this, I would want to delete the macros again. It seems that each time
I want to run this macro to delete the modules, I have to set up the
reference again. Is there any way to wirte this into the same macro that
deletes the modules or is there a way to leave this refernence on.
'------------------

If I understand correctly, after adding the reference to the VBA
Extensibility library, save the file.


---
Regards,
Norman

Rob,

You can dispense with the reference to the Extensibility library altogether
by declaring the objects As Object, and using the actual constant numbers
rather than by their symbolic name:

Change
Dim VBComp As VBIDE.VBComponent
Dim VBComps As VBIDE.VBComponents
To
Dim VBComp As Object
Dim VBComps As Object

Change vbext_ct_StdModule, vbext_ct_MSForm, vbext_ct_ClassModule
to
1, 3, and 2 repectively.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)



"Rob" wrote in message
...
One problem, I am going to be saving this workbook with no macros to
different file name. So the next month, I would open the original file
with
the macros and run those to perform the calculations. When I would finish
with this, I would want to delete the macros again. It seems that each
time
I want to run this macro to delete the modules, I have to set up the
reference again. Is there any way to wirte this into the same macro that
deletes the modules or is there a way to leave this refernence on.

Rob

"Norman Jones" wrote:

Hi Rob,

For additional information, see Chip Pearson's Programming To The Visual
Basic Editor page at:


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

---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Rob,

You need to set a reference the visual Basic Extensibility library:

VBA Menu | Tools | References


---
Regards,
Norman