Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Memory problem: Userforms and ActiveX controls: How many is too many?
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; ; 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.) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Memory problem: Need to set object variables for userforms? Forranges? | Excel Programming | |||
Maximum Controls on UserForms | Excel Programming | |||
ActiveX Controls vs Form Controls | Excel Discussion (Misc queries) | |||
activex controls | Excel Programming | |||
Releasing all Userforms in memory | Excel Programming |