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?

Any globally declared varaibles are by default static. Normal procedure
variables are created on the stack. The stack is emptied after the procedure
is finished. Global and static variables are created on the heap. The heap is
only unloaded when the project ends (when the spreadsheet is closed). There
is one excpetion to this. If you use the stand alone code line "End" that
clears the heap and all varaibles along with it.
--
HTH...

Jim Thomlinson


"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?

On 3 Jun, 21:25, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote:
Any globally declared varaibles are by default static. Normal procedure
variables are created on the stack. The stack is emptied after the procedure
is finished. Global and static variables are created on the heap. The heap is
only unloaded when the project ends (when the spreadsheet is closed). There
is one excpetion to this. If you use the stand alone code line "End" that
clears the heap and all varaibles along with it.

Interesting.
That is what I would expect, but not what I am seeing. I will check
through my program code for an "End" though (with 9000+ lines there is
plenty of scope for it to be hiding in a corner somewere)
However, in a much simpler bit of code I wrote recently I seemed to be
seeing the same thing. In that case a globally declared array of a
wrapper-class for an array of comboboxes was running the "Terminate"
event for each instance as soon as the code which created them was
completed. This either created them and instantly deleted them (with a
well-behaved "Terminate" routine) or left a bunch of orphan controls
on the worksheet (with no event handlers).
In that case I gave up and created the 15 comboboxes manually and copy-
pasted their event handlers 15 times each :-/

Thanks for the pointer, I will do some experiments. At the very least
I now have a new keyword to search on: "Heap"

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?

On 3 Jun, 21:46, Dave wrote:
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.

I already do that, the macro has half a dozen hidden sheets containing
config data. However, these variables are Objects. There might be some
fun to be had squirelling them away in the OLEObjects collection, but
that does seem a little Byzantine.

Ok, ok, thanks for the feedback
Dave.

"atpgroups" wrote:

On 3 Jun, 21:46, Dave wrote:
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.

I already do that, the macro has half a dozen hidden sheets containing
config data. However, these variables are Objects. There might be some
fun to be had squirelling them away in the OLEObjects collection, but
that does seem a little Byzantine.

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?

On 3 Jun, 23:32, "Jim Cone" wrote:
Other things...
Running code from the VBE can cause global variables to die.

OK, I will bear that in mind. Actually that might explain some of my
issues. Are you saying that invoking a procedure from the immediate
window or using run/F5 leads to a different behaviour to invoking the
code via a button or menu?

Global Variables should be declared in a standard module.

They are. In fact I have a whole module dedicated to globals,
constants, enums and DLL declarations.
There is no chance that the DLL calls are causing the module to look
different to the compiler?

Any time you are in code module and screwing with it the entire
project can be reset. Sometimes you notice it and sometimes not.
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"atpgroups"
wrote in message
On 3 Jun, 23:32, "Jim Cone" wrote:
Other things...
Running code from the VBE can cause global variables to die.

OK, I will bear that in mind. Actually that might explain some of my
issues. Are you saying that invoking a procedure from the immediate
window or using run/F5 leads to a different behaviour to invoking the
code via a button or menu?

Global Variables should be declared in a standard module.

They are. In fact I have a whole module dedicated to globals,
constants, enums and DLL declarations.
There is no chance that the DLL calls are causing the module to look
different to the compiler?

On 4 Jun, 02:58, "Jim Cone" wrote:
Any time you are in code module and screwing with it the entire
project can be reset.

Even more complicated than that, it seems...
Does anyone know of a definitive list of things that reset the heap?

For example, creating an OLEObject seems to reset the heap, try the
following as an example. (you need two command buttons linked to the
code). The collection increases in size while you add doubles to it,
then increases in size once more when you add a combobox. Then next
time you add a combobox it is back to count=1

Option Explicit
Public coll As New Collection

Private Sub CommandButton1_Click()
Dim v As OLEObject
Set v = Sheet1.OLEObjects.Add(Classtype:="Forms.Combobox.1 ")
coll.Add v
Sheet1.Range("A1").Value = coll.Count
End Sub

Private Sub CommandButton2_Click()
Dim v As Double
v = Timer
coll.Add v
Sheet1.Range("A1").Value = coll.Count
End Sub

I declared the public collection in a standard module without the New word
and then created an instance of it in a separate sub.
The collection still reset to nothing when I added the second combobox.
That is new to me.

In any case, I try to avoid public variables.
I will pass a collection object to other subs/function as needed an then
set it to nothing when exiting.
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"atpgroups"
wrote in message
On 4 Jun, 02:58, "Jim Cone"
wrote: Any time you are in code module and screwing with it the entire
project can be reset.

Even more complicated than that, it seems...
Does anyone know of a definitive list of things that reset the heap?

For example, creating an OLEObject seems to reset the heap, try the
following as an example. (you need two command buttons linked to the
code). The collection increases in size while you add doubles to it,
then increases in size once more when you add a combobox. Then next
time you add a combobox it is back to count=1

Option Explicit
Public coll As New Collection

Private Sub CommandButton1_Click()
Dim v As OLEObject
Set v = Sheet1.OLEObjects.Add(Classtype:="Forms.Combobox.1 ")
coll.Add v
Sheet1.Range("A1").Value = coll.Count
End Sub

Private Sub CommandButton2_Click()
Dim v As Double
v = Timer
coll.Add v
Sheet1.Range("A1").Value = coll.Count
End Sub