Background: I am building a VBA-based tool in Excel 2003 on Windows
XP. I'm having some kind of memory leak problem: I get "Out of
memory," with no mention of Error 7. This can occur simply when I am
in the VBE without any of my code running. Also, after the error
occurs, I am unable to save the workbook, even to a local drive. This
is the fourth of four posts with questions about what the source of
this error might be.
HIDING + UNHIDING FORMS?
I found one thread that seems particularly relevant: http://tinyurl.com/yv3srr
. The gist is that loading+unloading a UserForm can eat up memory. Is
this true? I have not seen it documented anywhere else.
Should I be loading the key forms once, then merely hiding+unhiding
them? Or would that actually increase memory load, since it means
having lots of forms in memory at once?
(NB: I use "<form name>.Show" to load the forms, then "Unload Me" from
within each form to close. For object variables (except possibly the
Userform object variables - see my previous post), I set them to
Nothing at the end of every procedure.)
TOO MANY FORMS?
My Excel workbook has 13 Userforms, of which at most three are open at
any given time. The forms are all modal. In the most complex use
case, the following three forms are all open at once:
Form 1 (main data management form): Eight command buttons, two radio
buttons, five frames (some nested three deep), and some labels.
Form 2 (main CRUD form): 16 command buttons (including Edit/Delete/
Create for each of five properties), five combo boxes, two radio
buttons, a checkbox and some labels. Also some empty frames, height 1,
used as dividers.
Form 3 (properties form for creating/updating a certain type of item):
four command buttons, four combo boxes, three frames and some labels.
I. Is this far too many ActiveX controls to have open at one time?
II. Would you suggest breaking the forms up into smaller forms?
III. If so, how would you organize them: allow for more levels of
"nesting" than three, or try to get them to work in sequence?
IV. Regarding the form with 16 command buttons: Would you try to stay
below the 64K segment boundary by moving most of the command-button
response code to a separate module? Or is the mere presence of so many
ActiveX controls likely to be the main problem here?
I am also wondering whether I need to set object variables for my userforms, and even for my range references, cf. http://tinyurl.com/33j7d8
Thanks in advance...