Deleting Macros

  • Thread starter Thread starter lj
  • Start date Start date
L

lj

I created a macro that saves a file as a particular date and then
removes all of the links in the new spreadsheet. What I would like to
do is also remove any trace of the macro in the new file, how do I do
this in visual basic as part of the macro script.
 
You could just copy the worksheets to a new workbook, delete the links then
save as your particular date.

Or see Chip Pearson's site for deleting Modules from a workbook.

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

Gord Dibben  MS Excel MVP





- Show quoted text -
Click to expand...


Is this what I would want to use (see script below)? When I try and
run it I get a compile error that "user defined type is not defined
for - "VBProj As VBIDE.VBProject"

Deleting A Module From A Project

This code will delete Module1 from the VBProject. Note that you cannot
remove any of the Sheet modules or the ThisWorkbook module. In
general, you cannot delete a module whose Type is vbext_ct_Document.



Sub DeleteModule()
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent

Set VBProj = ActiveWorkbook.VBProject
Set VBComp = VBProj.VBComponents("Module1")
VBProj.VBComponents.Remove VBComp
End Sub
 
Did you read and heed the instructions in Chip's introduction on that site?

..........................................................

In order to use the code on this page in your projects, you must change two
settings.

First, you need to set an reference to the VBA Extensibililty library. The
library contains the definitions of the objects that make up the VBProject.
In the VBA editor, go the the Tools menu and choose References. In that
dialog, scroll down to and check the entry for Microsoft Visual Basic For
Applications Extensibility 5.3. If you do not set this reference, you will
receive a User-defined type not defined compiler error.


Next, you need to enable programmatic access to the VBA Project. In Excel
2003 and earlier, go the Tools menu (in Excel, not in the VBA editor),
choose Macros and then the Security item. In that dialog, click on the
Trusted Publishers tab and check the Trust access to the Visual Basic
Project setting.

In Excel 2007, click the Developer item on the main Ribbon and then click
the Macro Security item in the Code panel. In that dialog, choose Macro
Settings and check the Trust access to the VBA project object model.

...............................................................

Gord
 
Back
Top