phberman
asked on
out of memory with userforms
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.
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?
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...
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.
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?
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...
ASKER
Also: For anyone interested, all of my Usenet info and queries can be found at http://tinyurl.com/2euf8g and at http://tinyurl.com/2e8j37
1. What RAM do you have installed?
2GB
2. What size is your PageFile set at?
According to System Properties/Advanced/Perfor
"Total paging file size for all drives: 2046 MB"
PF Usage is typically around 600MB according to Task Manager
3. What is the size of the workbook?
About 2MB. It goes down to about 1.4MB when I use Code Cleaner.
4. What other applications are you running at the same time?
It varies. Sometimes I have lots of open windows, including
Firefox which is a memory hog. I think the memory problem has
happened even when I did not have Firefox or many other
windows open.
5. Have you looked in the Task Manager to check the RAM usage?
Yes. When I first open the main workbook, Mem Usage goes up
to about 35MB. After working in it for a while, the memory gets
into the 40's. One time, the usage was around 55MB when I got
the memory error. I believe that the error also occurred once when it was
in the 40s.
1. What RAM do you have installed?
2GB
2. What size is your PageFile set at?
According to System Properties/Advanced/Perfor
"Total paging file size for all drives: 2046 MB"
PF Usage is typically around 600MB according to Task Manager
3. What is the size of the workbook?
About 2MB. It goes down to about 1.4MB when I use Code Cleaner.
4. What other applications are you running at the same time?
It varies. Sometimes I have lots of open windows, including
Firefox which is a memory hog. I think the memory problem has
happened even when I did not have Firefox or many other
windows open.
5. Have you looked in the Task Manager to check the RAM usage?
Yes. When I first open the main workbook, Mem Usage goes up
to about 35MB. After working in it for a while, the memory gets
into the 40's. One time, the usage was around 55MB when I got
the memory error. I believe that the error also occurred once when it was
in the 40s.
ASKER
I just thought of a specific thing that I'm doing in a few places:
* There's a command button on Sheet1. The click event code for that button includes "UserForm1.Show".
* There's a command button on UserForm1. The click event code for that button includes "UserForm2.Show".
* There's a command button on UserForm2. The click event code for that button calls some other routines that work with data, then says
Unload Me
Unload UserForm1
-----
As I look at it now, I'm thinking that this could be where my memory leak (at least one of them) lies. Perhaps what I need to do is this:
* There's a command button on UserForm1. The click event code for that button includes "UserForm2.Show," followed by "Unload Me."
* There's a command button on UserForm2. The click event code for that button calls some other routines that work with data, then says "Unload Me."
* There's a command button on Sheet1. The click event code for that button includes "UserForm1.Show".
* There's a command button on UserForm1. The click event code for that button includes "UserForm2.Show".
* There's a command button on UserForm2. The click event code for that button calls some other routines that work with data, then says
Unload Me
Unload UserForm1
-----
As I look at it now, I'm thinking that this could be where my memory leak (at least one of them) lies. Perhaps what I need to do is this:
* There's a command button on UserForm1. The click event code for that button includes "UserForm2.Show," followed by "Unload Me."
* There's a command button on UserForm2. The click event code for that button calls some other routines that work with data, then says "Unload Me."
>According to System Properties/Advanced/Perfor
"Total paging file size for all drives: 2046 MB"
I believe that is too small if you are running other applications. You do have a reasonable amount of RAM, all the same it would do no harm to have a larger Page File - say 2500MB or perhaps even larger.
"Total paging file size for all drives: 2046 MB"
I believe that is too small if you are running other applications. You do have a reasonable amount of RAM, all the same it would do no harm to have a larger Page File - say 2500MB or perhaps even larger.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Re: Page File, does it matter that I've never seen actual PageFile usage (as reported in Task Manager) go above 1MB? I was guessing that that means paging is not an issue.
Re: unloading instead of hiding, I have always used Unload, without using Hide at all.
Actually, I've gotten feedback on Usenet that suggests that my problem may be due entirely to code bloat/code corruption, as a side effect of editing in the VBE.
(Note on EE rules: I'm not sure how the awarding of points works, but if it turns out that that Usenet comment was a "milestone" for me, then it might be fairest for me not to award the full 500 points for this question on EE. I have not yet read the EE FAQ on this sort of thing; I will do so before closing this issue.)
I started using Rob Bovey's CodeCleaner very recently and have not experienced the memory problem since. Still, I can't rule out its reoccurrence.
Follow-up question: Does code corruption ONLY occur when I am working in the development environment (changing code and/or forms, and testing those changes)? If so, then that would be the best case: I could run CodeCleaner while developing, then hand the tool over to users when it's ready. Then I wouldn't have to worry that the users might experience the Out of Memory error.
Re: unloading instead of hiding, I have always used Unload, without using Hide at all.
Actually, I've gotten feedback on Usenet that suggests that my problem may be due entirely to code bloat/code corruption, as a side effect of editing in the VBE.
(Note on EE rules: I'm not sure how the awarding of points works, but if it turns out that that Usenet comment was a "milestone" for me, then it might be fairest for me not to award the full 500 points for this question on EE. I have not yet read the EE FAQ on this sort of thing; I will do so before closing this issue.)
I started using Rob Bovey's CodeCleaner very recently and have not experienced the memory problem since. Still, I can't rule out its reoccurrence.
Follow-up question: Does code corruption ONLY occur when I am working in the development environment (changing code and/or forms, and testing those changes)? If so, then that would be the best case: I could run CodeCleaner while developing, then hand the tool over to users when it's ready. Then I wouldn't have to worry that the users might experience the Out of Memory error.
phberman,
Code corruption/code bloat does not occur in my experience in the VBE. The file size does not increase unreasonably nor does memory usage. I believe that is a red herring. However your file may well have redundant elements in it.
As for reducing the points you cannot do that yourself. Instead may I suggest that if you believe you have found the solution yourself then you would be justified in asking for the question to be closed and for your points to be refunded. To do that I suggest you ask a free question at the following link:
https://www.experts-exchange.com/Community_Support/
Quote the URL for your question. In this instance it is:
https://www.experts-exchange.com/questions/22993862/out-of-memory-with-userforms.html?cid=238&anchorAnswerId=20391541#a20391541
Patrick
Code corruption/code bloat does not occur in my experience in the VBE. The file size does not increase unreasonably nor does memory usage. I believe that is a red herring. However your file may well have redundant elements in it.
As for reducing the points you cannot do that yourself. Instead may I suggest that if you believe you have found the solution yourself then you would be justified in asking for the question to be closed and for your points to be refunded. To do that I suggest you ask a free question at the following link:
https://www.experts-exchange.com/Community_Support/
Quote the URL for your question. In this instance it is:
https://www.experts-exchange.com/questions/22993862/out-of-memory-with-userforms.html?cid=238&anchorAnswerId=20391541#a20391541
Patrick
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I've (belatedly) read through the Help page. It looks like my question and some of my comments are an example of "many questions in one," which is a frequently-made mistake. My apologies! I've learned that a memory leak is hard to track down, and the answers to my questions seem to depend a lot on circumstances. Thanks to the commenters.
phberman - Thanks for the points - Patrick
A few basic question:
1. What RAM do you have installed?
2. What size is your PageFile set at?
3. What is the size of the workbook?
4. What other applications are you running at the same time?
5. Have you looked in the Task Manager to check the RAM usage?
Patrick