Remove code module automatically

[Eric van Uden]

Hello all,

I may be asking for the impossible, but is there a way to add a command at
the end of a macro that will delete this macro itself or the whole code
module it resides in, after execution?

I use template workbooks to generate enduser workbooks. Once these enduser
workbooks are saved, some of the VBA code from the template workbook has
outlived its purpose and I would like to delete this as part of the creation
process.

I hope I am making sense...

Thanks in advance for any replies!

Eric

 

[Bob Phillips]

Hi Eric,

Here is an example

Dim VBComp As VBComponent

MsgBox "hello"
Set VBComp = ThisWorkbook.VBProject.vbcomponents("Module2")
ThisWorkbook.VBProject.vbcomponents.Remove VBComp

You need to set a reference (in VBE, Tools>References) to the Microsoft
Visual Basic for Applications Extensibility library.

--

HTH

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

 

[Frank Kabel]

Hi
you may take a look at the following site:
http://www.cpearson.com/excel/vbe.htm

 

[Mike Fogleman]

See Chip Pearson's site about manipulating VB code:
http://www.cpearson.com/excel/vbe.htm

Mike

 

[Eric van Uden]

Hello Bob,

Thanks for the speedy, crystal clear and accurate reply.
It works like a charm! I suppose I will have to find out the portability of
the Microsoft Visual Basic for Applications Extensibility library reference
across OS and XL versions, but that is not a primary requirement at this
moment.

Much obliged,

Eric

 

[Eric van Uden]

Hello Frank,

I will be studying this material. Especially the security issues (code
module not to be protected, "Trust access to Visual Basic Project" to be
activated) that seem to be involved. Although I knew about Chip Pearson's
site, I hadn't found my way to this information yet!

Thanks for your help.

Eric

 

[Tom Ogilvy]

If you change
Dim VBComp As VBComponent

to

Dim VBComp As Object

you won't need the reference to the extensibility library.

 

[Eric van Uden]

Hello Tom,

Thanks a lot for your addition. This makes things even easier.
Very Nice.

Eric

 

[Eric van Uden]

Hi Mike,

Much appreciated.
As you will have noticed, Frank just beat you in the answering speedrace.

Thank you for replying!

Eric