W
WHA
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 second of four posts with questions about what the source of
this error might be.
I believe this error only occurred after I started working with
Userforms, so I suspect that the Userforms are involved. 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.
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.
Questions:
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?
Thanks in advance, WHA
FACTS. I am using:
MS Office Excel 2003, SP3
MS XP Professional Version 2002, Service Pack 2
Dell Inspiron 9400; Intel Core2 CPU; [email protected]; 998MHz, 2.00GB of
RAM.
My Excel workbook is about 2MB in size.
When the memory error occurred recently, EXCEL.EXE was taking up about
55MB of memory (according to Windows Task Manager).
Preliminary measuring of =INFO("memused") seems to show 1.2MB when
tool is first loaded, then to fluctuate between 2 and 3 MB. (I did not
test this when the memory error occurred.)
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 second of four posts with questions about what the source of
this error might be.
I believe this error only occurred after I started working with
Userforms, so I suspect that the Userforms are involved. 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.
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.
Questions:
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?
Thanks in advance, WHA
FACTS. I am using:
MS Office Excel 2003, SP3
MS XP Professional Version 2002, Service Pack 2
Dell Inspiron 9400; Intel Core2 CPU; [email protected]; 998MHz, 2.00GB of
RAM.
My Excel workbook is about 2MB in size.
When the memory error occurred recently, EXCEL.EXE was taking up about
55MB of memory (according to Windows Task Manager).
Preliminary measuring of =INFO("memused") seems to show 1.2MB when
tool is first loaded, then to fluctuate between 2 and 3 MB. (I did not
test this when the memory error occurred.)