VBA Event to Restore Lost Public Variables

[David McCulloch]

QUESTION:
I tried to define global variables in a module with the Public statement.
Unfortunately, their values were eventually lost (possibly because I clicked
Excel's debug reset button or because the application that I inherited uses
an End statement?). Whatever the cause, what is a good way to reinitialize
them? I have a function to do that, but when would I run it? I already call
it from my workbook's open event. Is there an event that runs immediately
after Public variables are lost or before any other event would run? It's a
large application, so I would not want to start every function with a check
of my public variables.



OPTIONAL READING:
Normally, I would use Public Const statements to declare my constants, but
this is a multi-workbook application where workbooks must be able to access
one another. Today, each workbook has each other's constants embedded
within it. When one workbook's constants are changed, all other workbooks
must be updated accordingly and released along with it. To simplify, I want
to create a sheet named GBL in each workbook to hold that workbook's
constants (column-1 would be variable name; column-2 would be variable
value). When one workbook needed to access another workbook, the first
workbook would read the second workbook's GBL sheet and its constants (or at
least the ones that the first program needed). This way, when I must change
a workbook's constants, I would not have to update and release all other
workbooks.

Dave

 

[Bob Phillips]

Stick all of your globals in a class as properties, and initialize it like
so

Set myApp = New clsApp

and access the properties like this

Range("A1").value = myApp.Name

Then you just check myApp for nothing and just rerun if so

If myAPp Is Nothing Then Set myApp = New clsApp

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[FSt1]

hi
variables are volitale. once code stops, they're gone. you can not store
variables indefinitely like you do code. each time you run code you have to
redeclare you variables. that is why you declare variables at the start of
code so that vb will know what they are.

Sorry.
FSt1

 

[Jim Thomlinson]

An end statement would do it or possibly some kind or error. That being said
there is nothing that will tell you that the values are empty except checking
each one as it comes up. Yet one more reason why I avoid global variables
wherever possible.

 

[David McCulloch]

I accept the fact that variables, unlike constants that are declared with
Public Const, can be lost (although variables defined with the Public
statement hold their value across user events and are rarely initialized).
My problem is what to do about it in the situation where one workbook's
constants must be shared with other workbooks that can be released to my
users at different dates?

It's a large application, so I don't want to check the status of those
variables every time I reference them (such as at the beginning of each
routine, etc.). I was hoping, perhaps not realistically, that there was a
single event that could handle the check.

Dave

 

[David McCulloch]

I agree. I am converting my workbook's constants (declared via Public
Const) to variables so they can be better accessed by other workbooks that
are open at the same time. That way, I will eliminate redundant definitions
and I will introduce a level of indirection that allows me to upgrade one
workbook and its constants without having to distribute all workbooks with
the same release.

Dave

 

[Gary''s Student]

Anticipate the loss or at least understand it.

Can the variables be stored away in worksheet cells?

 

[David McCulloch]

Yes - the variables can be stored away in worksheet cells. I am moving my
constants from a global module that used Public Const declarations to a
dedicated worksheet so the variables can be accessed by other open workbooks
as well as the current workbook. Upon load, a function reads the worksheet
names/values and, using a SELECT CASE, loads the values into Public
variables.

I was hoping for something more elegant. Based upon the feedback I've
received here, it seems that I am doing things about as well as I can. Any
other thoughts that might be useful?

Dave

 

[Tim Zych]

Some other ideas:

- Refresh them frequently. If they are accidentally destroyed, they will be
recreated during the next event, say a sheet activate event, or whatever
occurs often enough for your comfort. You may also consider tying in the
refresh to error handling.
- Use a code workbook or addin to perform the code behind the scenes against
data-only workbooks (presumably the active workbooks), and keep the public
constants in the code workbook.
- Get rid of the End statements.
- Store them as public constants, and have your initilization macro(s) copy
that module to the code workbooks as needed.

 

[David McCulloch]

Tim,
I don't understand what you mean by "Store them as public constants, and
have your initilization macro(s) copy that module to the code workbooks as
needed." Are you saying that it is possible for one workbook to copy
another workbook's module along with its Public Constants? If so, that
might be the best possible solution. Public Constants are never lost, so I
would only have to worry about dynamically copying constants (i.e., at
runtime) when establishing a link from one workbook to another.

Dave

 

[Tim Zych]

Yes, but as you will see, it may or may not be the optimal solution
depending on your environment.

Depending on which version of Excel you are using, you may need to modify
the security settings, such as trusting programmatic access to Visual Basic
projects.

For code on how to copy a module to another workbook see
http://www.cpearson.com/excel/vbe.aspx

Note: there is a warning on the page about antivirus programs that detect
code that modifies the VBE, so heed it. If you run into a problem, let me
know as I have discovered a simple way to throw off Norton's Bloodhound
Heuristics, which may or may be applicable with other antivirus programs.

 

[M. Authement]

What I normally do is declare a global boolean variable, bIsInitialized. In
the initialize procedure/function where I set the values, I set
bIsInitialized to True. Then, in other functions I have a line such as If
Not bIsInitialized Then Call InitProcedure.