Deleting A Selected Macro from within a Module

[DS]

Good morning all, hoping someone can assist with the following:

I'm effectively looking for a method to delete individual subs from within a
module.


3 subs, A, B & C in a single module.

Sub A is run, which in turn calls EITHER sub B or C based on user-defined
parameters.

I require a method to instruct Sub B to delete Sub C when run, and vice
versa. As Sub C is not required if Sub B is run etc.

I have tried adapting the technique for deleting a module, replacing the
module name with the sub name, but, as that doesn't work, I'm somewhat at a
loss!

Because of variables being passed Sub B -> Sub A or Sub C -> Sub A, I need
to keep them in the same module, so simply splitting them into separate
modules and deleting those modules isn't really an option.

Many thanks in advance,
DS

 

[Norman Jones]

Hi D,

See Chip Pearson at:

Deleting A Procedure From A Module

 

[NickHK]

Whilst it is certainly possible to delete VBA code with code:
http://www.cpearson.com/excel/vbe.htm

you have to "Trust Access to VBA Project" and unless your code is correct,
you can end up with corruption.
In this situation, it hardly seems worth it, for the sake of one routine.
Will it matter if it left there, albeit unused ?

Surely if these "user-defined parameters" are changed, the now-deleted
routine cannot run.

Another option is to put the code in your Personal.xls and run the
appropriate code on the target WB.

NickHK

 

[DS]

Thanks Norman - shoulda known Chip would have something there!

Cheers
DS

 

[DS]

Hi Nick,

I'm going with Chip's code, thanks for the pointer. Personal.xls isn't
really an option as it will be used by multiple parties.

In this instance, the code is in "one-shot" read-only workbook which e-mails
a copy of itself to another party. Superfluous routines, sheets etc are
removed from the e-mailed copy prior to the temp copy being saved for mail,
to send a "static" result workbook, rather than a full working copy of the
"template" workbook which produces it, to the recipient. This saves on e-mail
server resource, reduces the chance of a failed sending due to either sender
or recipient having insufficient room in their e-mail inbox, and ensures that
no accidental running of code (via Tools->Macro etc) can be actioned by the
recipient to activate code which refers to (now) non-existent worksheets etc,
which would risk damage to the received copy.
So yeah, it matters!!! =;-)

Cheers
DS