Perhaps one of you ran into and knows of a subtile "gotcha" in this
regard. I hope I don't get tongue tied in terminology here. I'm
sure the code is way too complex to paste here.
Short version:
A complete array [ three dimensions 108 elements total), gets nulled
out after filling it and I don't seem to be doing it. I feel that
while I define the array to have a scope for the whole project, VBA
is nulling it out - perhaps because it is not valid to assign values
in a form then use the values outside the form without some esoteric
enabler. { not too hard to find something esoertic with me at the
VBA controls}.
Long Detail:
I have a String array [three dimensions: ArrayName(9,4,3)] that keeps
getting nulled out... as in, empty entries/elements [= ""]. I define
the array in a Public "Dim" in one of my "regular" modules [module
level] (which resides in the "Modules" area of the VBA explorer).
It gets filled up with values in a "preferences" Form (residing
in the "Forms" area of the VBA Explorer). I fill the array elements
(from text boxes in the Form) before Hiding the form -- this code is
_IN_ the Form code. In the Form code, before Hiding the form, if I
Call a Sub (which resides in a regular module) that then Debug.Prints
the array elements, it shows that it is loaded A-OK. Then the
Form.Hide works and I am looking @ my spreadsheet.
Now hit a button or checkbox on my sheet which fires a Sub that
uses the data in the array and it is all null.
I tried placing the array's Public "Dim" in various (one or more)
modules/forms. (it can be in more than two places and not cause an
ambiguity error depending where you put it). Some places seem to
cause the 'nulling' more frequently than others.
Where I have it now (regular Module), it seems to not null-out as
much. I also use an Immediate "Print ArrayName(0,0,0)" to test
things immediately after hiding the Form and it is still full.
Early-on (with it defined in a regular Module) the immediate print
showed that it had been nulled. I now have that working, but hitting
the button/check box still results in a nulled out array. I re-read
4-5 pages from MSDN all about variable scope, lifetime, etc. and
AFAIK I have it 'global' and it *should* stay put between Hiding the
Form and executing the Sub in the Module, but alas....
I put a Watch on one of the array elements, but it does not show a re-
assignment (assuming I did this correctly) , so it appears that I am
not inadvertently 86'ing it in some fat-fingered code.
To mitigate:
Right now, every time before I use the array, I test one element for
a null and reload it (in a Sub - the same Sub that the Form uses to
load it) if it is null, but think it should stay filled untill I tell
it or reset the macro editor/enviromrnt or have an error, no??
I sprinkled Debug.Prints all over and even did a Sub to Debug.Print
the whole array and sprinkle Calls to it all over - including in the
Immediate window. (it shows a blank array if placed first in the
Module where it's used) I usually manage to fix the self-inflicted
problems I so efficiently create, but ..... . . . .