Hi,
Not sure if this is what you're after, but sometimes when I want to preserve
a variable, even after a workbook is closed, I store it in an out-of-the-way
cell. You have to get the macro to call it from that cell every time it runs.
Dave.

"atpgroups" wrote:

Is there any way to create a super-static variable?

I have a few global variables which provide OLE connections to some
external applications that Excel is interacting with. It takes some
time to create the links. A global variable seems emminently suitable
for this purpose, after all nobody complains that Excel.Application is
a global, do they? My global variable is another application object.

I have a number of menu items and command buttons, all of which call
individual code modules. These typically interact with the external
application and run to completion.

As far as I can see global variables "disappear" once the procedures
complete. ie, if the title bar of the VB editor doesn't say
"[Running]" or "[Break]" then the variables are not available.

The effect of this is that when the next button is pressed or menu
item selected I have to repeat all the initialisation steps, OLE link
creation, etc.

As far as I can see I can't declare global variables as Static.

I can see two solutions, neither particularly appealing.
1) Embed a static copy of the required variables as a local variable
in each procedure which needs them, and perhaps try to keep them in
synch through globals
2) Run a thread somewhere along the lines of While 1 / DoEvents / Wend
to keep the code alive.

Am I missing something?