Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Keeping Variables Alive
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Keeping Variables Alive
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Keeping Variables Alive
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Keeping Variables Alive
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" |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Keeping Variables Alive
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Keeping Variables Alive
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Keeping Variables Alive
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? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Keeping Variables Alive
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? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Keeping Variables Alive
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? |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Keeping Variables Alive
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Keeping Variables Alive
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Keeping Variables Alive
On 4 Jun, 13:14, "Jim Cone" wrote:
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. |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Keeping Variables Alive
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. -- HTH... Jim Thomlinson "atpgroups" wrote: On 4 Jun, 13:14, "Jim Cone" wrote: 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. |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Keeping Variables Alive
On 4 Jun, 17:21, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Keeping Values in Variables | Excel Programming | |||
keeping a shape constant regardless of changes in variables | Excel Discussion (Misc queries) | |||
Keeping variables in a formula | Excel Discussion (Misc queries) | |||
Keeping a Variables value alive. | Excel Programming | |||
Keeping track of Global variables | Excel Programming |