SURELY macros still work after exiting the workbook they live in...er, don't they?

[baobob]

I've written some little useful functions and keyboard macros (e.g.
Shift-Ctrl-B to make cell font blue, Shift-Ctrl-W to wrap text, etc.)

I often keep several workbooks in memory at a time. But if I exit the
workbook that contains the macros, they seem to disappear from memory
and stop working.

Can't you keep some image of them in memory? Can't you, say, keep
their VBA Module in memory, without also having to keep the user data
in their book in memory, as well?

Which is another way of saying: What I really wanna do is load the
macros in an autoexec workbook, then jettison the book.

Can I do it? Or do I need to keep the book parked in memory?

Thanks much.

***

 

[dan dungan]

enter them in personal.xls

 

[Otto Moehrbach]

No. They are a part of the file. When the file closes, everything in the
file closes. HTH Otto

 

[JLGWhiz]

putting together what Otto and Dan said. The file that contains the macro
must be open for the code to execute. However, the code can manipulate,
calculate, add, delete and anayze data in other workbooks while it is open.
Dan's suggestion to use the Personal.xlm file will allow you to put all of
your public macros in one place and run them from that file. This would
allow you to have various other workbooks that can use the same public macro
without having to have it in the same workbook as the data. Of course, any
event code for workbook or worksheet would have to be in the appropriate file
as a Private Sub.

Most of this information is available in the VBA help files, if you type in
the right keywords for the search. You can also find web sites that give
more details on the subject if you use the Google or Yahoo search engines.

 

[baobob]

Thanks Dan, Otto & JL GWhiz, for replies.

So you're telling me I gotta keep the data portion (even if empty) of
the sheet holding my macros in memory.

That is part of a larger objection I have to Microsoft's
implementation of macros, document automation, and even user
defaults..

For example, in Word, it ENRAGES me that Tools / Options is disabled
unless you have a document open--even an empty one. Half the user
defaults in Options are NOT document-specific, for Chrissake.

I guess I'm arguing for a "VBA engine" of some sort, that can sit in
memory independently. Which I suppose MS doesn't have.

Thanks all again.

***