Removing Modules

  • Thread starter Thread starter Nigel
  • Start date Start date
N

Nigel

I have a template xlt file containing a VBA code module, this is opened,
data is added under the control of another workbook and the new workbook is
saved under a new name. No problems so far.

The new workbook is subsequently opended and the code contained in the
module, originating from the template, is run which includes some code to
email the workbook to a number of recipients. Now comes the question.

I wish to remove the module from the workbook before it is emailed. Since
the removal of the module, the one running the email routine, is the module
in question can this be done?

TIA
Cheers
Nigel
 
Hi Nigel,

Isn't it odd how these things repeat. We've already had this question today.
ANyway, here is the answer

Here is an example

Dim VBComp As Object

Set VBComp = ThisWorkbook.VBProject.vbcomponents("Module1")
ThisWorkbook.VBProject.vbcomponents.Remove VBComp

Change ThisWorkbook to the appropriate workbook, such as
Workbooks("myMailer.xls").

--

HTH

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

Nigel said:
I have a template xlt file containing a VBA code module, this is opened,
data is added under the control of another workbook and the new workbook is
saved under a new name. No problems so far.

The new workbook is subsequently opended and the code contained in the
module, originating from the template, is run which includes some code to
email the workbook to a number of recipients. Now comes the question.

I wish to remove the module from the workbook before it is emailed. Since
the removal of the module, the one running the email routine, is the module
in question can this be done?

TIA
Cheers
Nigel





----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption
=---
 
This will not remove the module, but rather all the code within:

Public Sub smoo()
With ThisWorkbook.VBProject.VBComponents("Module2").CodeModule
.DeleteLines 1, .CountOfLines
End With
End Sub

- Piku
 
Hi Bob
I had not read the earlier post so it is curious!

Are you suggesting that the module that contains this code can remove
itself?

How does that work, is the entire code held in memory awaiting execution or
does the module only get removed when the code it contains complete? I
must admit I have not tried it yet but I am trying to understand it first.

Cheers
Nigel
 
Thanks for the guidance, if the module remains with no code in it, does the
workbook macro trigger the macro warning on opening?

Cheers
Nigel
 
Yes.

--

HTH

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

Nigel said:
Thanks for the guidance, if the module remains with no code in it, does the
workbook macro trigger the macro warning on opening?

Cheers
Nigel






----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption
=---
 
HI Nigel,

Surprisingly, yes I am suggesting just that.

As you say. The code is pre-compiled, so as long as it is not huge, it would
be easy to store in memory, and then deleting it is removing the actual
module object and is not a problem.

It seems strange, but try it, it works.

--

HTH

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

Nigel said:
Hi Bob
I had not read the earlier post so it is curious!

Are you suggesting that the module that contains this code can remove
itself?

How does that work, is the entire code held in memory awaiting execution or
does the module only get removed when the code it contains complete? I
must admit I have not tried it yet but I am trying to understand it first.

Cheers
Nigel






----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption
=---
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top