Public array is losing data

I'm using a large array that is dimensioned as public. I'm using thearray to hold a large quantity of data throughout the users workingsession so that I can build reports rapidly without having to read fromother workbooks. It works great except that the array keeps losingit's data in a seemingly random fashion. I can exercise a sequence ofVBA routines and everything is fine. But sometimes the same sequenceof routines will cause the array to lose it's data and I can't figureout what's causing it. All of the code is in one module plus severaluser forms and some code in ThisWorkbook. Any suggestions?

Share:

2 Answers Found

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

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

You need to make your program tolerant of this. Easiest way is to havea public Boolean with your other public variables that indicates thatthe 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.