Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA variables retaining their values
I have a VBA application which uses global variables defined at the top of
the Module. I have found that if I leave the applpication and go back to the regular mode of usage of Excel, then if I rerun the app the globals seem to retain the vaules they were given the last time I ran it. Can I count on this? Is there any way to ensure that this will always be the case? -- -regards |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA variables retaining their values
Global variables will hold their values for the duration that the spreadsheet
is open with a few exceptions. If you stop the execution in the VBE then the global variables will be cleared. If you use the stand alone line of code "End" then global variables will be cleared. If your code ends due to an unhandled error then global variables will be cleared. All of that being said you want to minimize your use of global variables as they can be a nightmare to debug if they end up by holding values that are incorrect. The problem is that since the variable is global it can be very difficult to trace which sub or function changed the variable to the incorrect value. Another way to make a variable ho0ld it's value is to declare it within a procedure as Static. -- HTH... Jim Thomlinson "GeorgeJ" wrote: I have a VBA application which uses global variables defined at the top of the Module. I have found that if I leave the applpication and go back to the regular mode of usage of Excel, then if I rerun the app the globals seem to retain the vaules they were given the last time I ran it. Can I count on this? Is there any way to ensure that this will always be the case? -- -regards |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA variables retaining their values
Thank you Jim. I understand the pitfalls of global variables but sometimes
they are necessary. In the particular app I am writing (an in house specialized acounting program) in which I have need of globals for such thing as letting the system know how many departments and how many accounts there are, etc. Of course I could have these data stored in cells, but it would be cumbersome to require the code to always read these variables in each time the user re-entered the program mode. For some operations, such as, for example, entering new entries, this app will have the user leave the program mode and go into direct mode, where he will enter info into cells of a particular worksheet directly, then he will re-enter the module by pushing a button labeled "Accept". It will be helpful to, at this point, not have to have the program reread system wide parameters from cells in the spreadsheet. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting a worksheet but retaining values from the worksheet. | Excel Discussion (Misc queries) | |||
Deleting a worksheet but retaining values from the worksheet. | Excel Discussion (Misc queries) | |||
how to look up values with two independent variables | Excel Worksheet Functions | |||
Formula to Check Values between to variables | Excel Worksheet Functions | |||
Chart two variables with differing values | Charts and Charting in Excel |