VBA variables retaining their values

[Guest]

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?

 

[Guest]

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.

 

[Guest]

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.