Keeping Variables Alive

[atpgroups]

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?

 

[Jim Thomlinson]

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.

 

[Dave]

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]

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"

 

[atpgroups]

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.

 

[Dave]

Ok, ok, thanks for the feedback
Dave.

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.

 

[Jim Cone]

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" <[email protected]>
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?

 

[atpgroups]

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?

 

[Jim Cone]

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" <[email protected]>
wrote in message

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?

 

[atpgroups]

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

 

[Jim Cone]

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" <[email protected]>
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

 

[atpgroups]

The collection still reset to nothing when I added the second combobox.
That is new to me.

I am glad it isn't just me going mad then.

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.

I don't really see that I have much option here. There is an
initialisation phase which checks the data and builds an array of OLE
links to data items in a third-party application (which in turn links
to embedded code in an engine conrtoller, which links to a dynamometer
controller...). At some future point the user can hit a second button
and start the test. In the meantime there is no thread running. I
don't want to go through the time-consuming data check and array
creation process every time, but there is no thread active to pass the
data to the second-phase code when the user presses the button. I am
not using the globals for their globality so much as for their static-
ness. Or that was the plan anyway.

 

[Jim Thomlinson]

Just my two cents but you are using globals for the wrong reason. You are
better off creating the collection when it is needed than trying to keep the
collection stored globally. The code to create the collection is relatively
easy and very quick. By dynamically creating the collection when you need it
you alway get a correct list of controls.

That being said I personally almost never use global variables. About the
only time I use them is if I have an application that needs to have passwords
to access databases. I store the user info and password globally. I find
globals a real pain to debug. When they go wrong you just never know what
process messed them up.

 

[atpgroups]

Just my two cents but you are using globals for the wrong reason. You are
better off creating the collection when it is needed than trying to keep the
collection stored globally. The code to create the collection is relatively
easy and very quick.

I think you may be confusing the example I created to show some wierd
behaviour of global variables with the actual application that I am
writing. Creating the application object and the dozens of child
objects and checking data validity in the actual application is far
from fast, it can take 10 minutes. It also creates an annoying amount
of disruption in the server application UI while it is doing it too.

What I am doing is using sets of values stored in a set of excel
sheets to set combinations of values in a separate application more
rapidly and accurately than a human can do it. This requires an object
which provides an interface between Excel and the OLE server, and an
array of sub-objects, one for each onscreen variable to be adjusted.
There are 10,000 variables that could be adjusted, though in practice
the users will only be performing experiments on 20 or so at a time.
There is a certain amount of overhead in the interaction, which can be
minimised by creating the interface objects ahead of time. Most of the
"variables" are not simple scalar values, they are 3D maps which the
interface objects allow me to manipulate.

See it as equivalent to not creating an Excel application object and a
Sheets collection every time you want to look at a cell value. I want
a persistent array of objects which I can interrogate and set values
through.