Solved

out of memory with userforms

Posted on 2007-11-30
11
1,182 Views
Last Modified: 2010-04-21
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...
0
Comment
Question by:phberman
  • 5
  • 4
  • 2
11 Comments
 
LVL 45

Expert Comment

by:patrickab
ID: 20387712
phberman,

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
0
 

Author Comment

by:phberman
ID: 20390164
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/Performance Options,
"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.
0
 

Author Comment

by:phberman
ID: 20390342
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."
0
 
LVL 45

Expert Comment

by:patrickab
ID: 20390864
>According to System Properties/Advanced/Performance Options,
"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.

0
 
LVL 45

Assisted Solution

by:patrickab
patrickab earned 100 total points
ID: 20390867
I believe it is wise to unload UserForms not just hide them as they do use RAM and perhaps that's what's happening. That's all the more helpful if you have many UserForms.
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:phberman
ID: 20391541
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.
0
 
LVL 45

Expert Comment

by:patrickab
ID: 20392350
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:

http://www.experts-exchange.com/Community_Support/

Quote the URL for your question. In this instance it is:

http://www.experts-exchange.com/Programming/Misc/Q_22993862.html?cid=238#a20391541

Patrick
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 400 total points
ID: 20395194
You should definitely be using object variables when programming with userforms. It avoids the possibility that the form you refer to is not the instance you think it is - e.g. you unintentionally unload a userform then refer to one of its properties which implicitly creates a new instance of it.
I would also hide rather than unloading any form that is likely to keep being reloaded.
Regards,
Rory
0
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 400 total points
ID: 20395300
PS Code corruption and bloat can and do occur after repeated editing of modules/objects. One reason you should keep as much code as possible in normal, class or userform modules (as opposed to worksheet/workbook modules) is that you can clean them easily by exporting and importing.
Regards,
Rory
0
 

Author Closing Comment

by:phberman
ID: 31411990
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.
0
 
LVL 45

Expert Comment

by:patrickab
ID: 20400017
phberman - Thanks for the points - Patrick
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

This is an explanation of a simple data model to help parse a JSON feed
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now