MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Public array is losing data

  Asked By: Annie    Date: Dec 29    Category: MS Office    Views: 1790

I'm using a large array that is dimensioned as public. I'm using the
array to hold a large quantity of data throughout the users working
session so that I can build reports rapidly without having to read from
other workbooks. It works great except that the array keeps losing
it's data in a seemingly random fashion. I can exercise a sequence of
VBA routines and everything is fine. But sometimes the same sequence
of routines will cause the array to lose it's data and I can't figure
out what's causing it. All of the code is in one module plus several
user forms and some code in ThisWorkbook. Any suggestions?



2 Answers Found

Answer #1    Answered By: Rhys Evans     Answered On: Dec 29

The public  information is only there for the VBA session, not the Excel

I.e. if, for any reason, Excel decides to rebuild the VBA, then your
public information will be gone. It'll also be gone whenever Excel
simply starts VBA up again - which it does on regular occasions, if
you're not actually running any VBA at the time.

You need to make your program tolerant of this. Easiest way is to have
a public Boolean with your other public variables that indicates that
the data  has been loaded. This will default to false when VBA starts.

If you want any more permanence than this, shove the data into a
(probably hidden) worksheet.

Answer #2    Answered By: Mildred Bailey     Answered On: Dec 29

Many thanks. That accounts for the unpredicable behavior.

Didn't find what you were looking for? Find more on Public array is losing data Or get search suggestion and latest updates.