Excel loses mind clearing VBA global variables

[Pictou]

I’ve inherited a large Excel and Access application that has been grown over
time by a number of individual developers. The major issue I need to resolve
is the use or abuse of global variables. This application uses VBA global
variables rather than storing data on hidden worksheets. Also, all data is
loaded into arrays – VBA global variables – from an Access database when the
application starts. There are many places in the code that checks that the
global variables are still populated and if not then the application is
automatically restarted.

I can not redesign and rewrite this application from scratch. I have to find
ways to mitigate the risk of a restart. I need a list of things, events, etc.
that can cause Excel to clear VBA global variables so I can “dance aroundâ€
these “things.â€

Inserting objects onto a sheet may be one thing. Adding and deleting sheets
may be another. My problems began with using VBA to insert a combo box onto a
sheet right after code that inserted action buttons. I now am using a
template and have removed the code that inserts these objects. VBA adds a new
sheet using the template. All the objects and formulae work. The data in the
global variables is present and is inserted into the new sheet. Now when I
click the menu option to do the same process again, all the global variables
are cleared. The first step of this process looks for sheets left from
previous executions and deletes them. This appears to be the place where
Excel resets. Also, Excel announces at the point where a sheet is to be
deleted that break mode can not continue and provides an option to continue
code execution or end. Is there a way to preserve Excel’s state through sheet
deletion or should I go through the application and see about reusing sheets
rather than deleting them?

 

[Jim Thomlinson]

The first thing to look for is the stand alone code line "End". That one
clears globals. Other than that I know of nothing specific that clears
globals but then again I avoid globals wherever possible. I would suspect
that code which adds or deletes code would be an issue.

 

[Pictou]

First thing I did was look for standalone "End" statements. I have narrowed
the issue to delete of sheets and charts from the workbook. That seems to
cause an implicit "End." Of course, the version of the application from
before I started is working fine with no clearing of global variables.

I inherited this application so I have to find mitigating strategies. Right
now I plan to hide the unneeded sheets and charts rather than delete them and
change the code to look for existing sheets and charts and reuse those rather
add new ones.

Since the application was "working" before I got into it, I am the goat who
has broken it.