Ive 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 Excels state through sheet
deletion or should I go through the application and see about reusing sheets
rather than deleting them?