Other things...
Running code from the VBE can cause global variables to die.
You get much better consistency with it all tightened down.
And back to basics, just in case...
Global Variables should be declared in a standard module.
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


"atpgroups"
wrote in message
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?