Memory problem: Am I hitting the 64K segment boundary?

[WHA]

See also http://www.dailydoseofexcel.com/archives/2004/10/13/module-size-limits/

I was interested in knowing whether a specific code module was over
this limit. This was the code module for my main CRUD form. This form
has 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. There are
1330 lines of code but most of these are commented out.

Per the comments at the above site, I tried
?
len(application.VBE.ActiveCodePane.CodeModule.Lines(1,application.VBE.ActiveCodePane.CodeModule.CountOfLines))/
1000

in the Immediate window and got 47.847. (Note for posterity: I first
had to go to Tools/Macro/Security/Trusted Publishers/Trust access to
VB Project.) Is that bad? Is 47.847 the result of compiling the code?
Or, instead, can that number be affected by the user opening/closing/
using the Userform?

Thanks in advance, 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 third of four posts with questions about what the source of
this error might be.

 

[Peter T]

From what you describe, a handful of controls and total length of code 47k
characters in 1330 line including many commented lines does not seem large
at all.
Indeed in XL2002 and later you would have had to tick that security box to
reference the VBIDE, I'm sure unrelated to your problems, no not bad.

It sounds like a problem in your coded leading to memory leaks which can be
problematic to track down.

What does that Delete and Create do

Frames are relatively resource intensive, try removing them, but unless
linked to something else you are doing I'm not optimistic that'll help.

Regards,
Peter T

 

[WHA]

Thank you for the reply -- the Edit/Delete/Create functions are for
records that are stored in a separate Excel workbook ("data
workbook"). The data workbook has tabs for State, City and
Neighborhood, and the main CRUD form has combo boxes for each of these
three items too. (Names changed to protect the innocent.) When the
user changes the State combo box, the system (re)populates the City
combo box; likewise with City and Neighborhood. Next to each combo box
are three buttons: Edit Selected, Delete Selected, and Create New;
these lead to separate UserForms that do the corresponding functions.

Most of the edit/delete/create functions are commented out as of now
(so clicking most of the buttons does nothing) - and I still got the
out-of-memory error.

I have more info at http://tinyurl.com/374w7w ("Memory problem: Out of
Memory and cannot save") on possible sources of the memory leak.

 

[Peter T]

I vaguely get the gist from those other threads. Maybe, as you suspect, you
are not releasing your form objects correctly. In your other thread it might
be an idea show how they are opened & closed.

Regards,
Peter T

Thank you for the reply -- the Edit/Delete/Create functions are for
records that are stored in a separate Excel workbook ("data
workbook"). The data workbook has tabs for State, City and
Neighborhood, and the main CRUD form has combo boxes for each of these
three items too. (Names changed to protect the innocent.) When the
user changes the State combo box, the system (re)populates the City
combo box; likewise with City and Neighborhood. Next to each combo box
are three buttons: Edit Selected, Delete Selected, and Create New;
these lead to separate UserForms that do the corresponding functions.

Most of the edit/delete/create functions are commented out as of now
(so clicking most of the buttons does nothing) - and I still got the
out-of-memory error.

I have more info at http://tinyurl.com/374w7w ("Memory problem: Out of
Memory and cannot save") on possible sources of the memory leak.

See also

 

[WHA]

(continuing in this thread for clarity) I thought I did show how the
userforms are opened&closed? I wrote

(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.)

The previous post is at http://tinyurl.com/29nzfz -- briefly, it's
about whether I ought to define, set, then clear an object variable
for a userform as opposed to just using "UserForm1.Show" and "Unload
Me."

 

[Peter T]

My original response was to answer that IMO the overall size of your form,
code & controls, was in no way excessive. I admit thereafter I may not have
fully absorbed all of the various threads. However use of "Unload Me"
doesn't necessarily release destroy the form. Set obj = Nothing will
disconnect the reference from the object but would only destroy the object
if there are no other references to the object. IOW so what you had revealed
is not really enough to confirm either way.

Sometimes those references are not obvious, eg if a second form is launched
from the first you can't destroy the first form until unloading the second
(with modal forms). It may well be that there's nothing wrong at all with
your method to destroy the forms, problems may well stem from elsewhere. Try
adding the following to all your forms -

Private Sub UserForm_Initialize()
Debug.Print "Initialize " & Me.Name
End Sub

Private Sub UserForm_Terminate()
Debug.Print "Terminate " & Me.Name
End Sub

You should get matching pairs.

Regards,
Peter T

 

[WHA]

Oh, no worries! I wasn't angry at all, just confused. Which happens
often

I am trying the initialize&release logging that you suggest. Thanks
again for looking at my posts!

WHA

 

[WHA]

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
-----
I have your suggested initialize and terminate logging code in place.
According to the log, things go as normal (init UF1; init UF2; term
UF2; term UF1). But could there still be some sort of memory leak
going on?
-----
See, I'm thinking that code like the above 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."

Might this make a difference?

 

[Peter T]

This bit -

* 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

and what I said previously -

" eg if a second form is launched from the first you can't
destroy the first form until after unloading the second
(with modal forms). "

which is just what you are trying to do above.

Try commenting "Unload UserForm1", that's in UF2, and in UF1 add the unload
line after the line show UF2

UserForm2.Show ' code will pass into UF2
Unload Me ' code will come back here when UF2 unloads

Looking at the Terminate, as I suggested, may have given a false sense of
confidence that the form was being fully unloaded. If you step through your
original code in UF2, starting at "Unload Me, you will see the two Terminate
events fire, then the code jumps back into the now supposedly non-existent
UF1.

This could indeed be at the route of your problem. However it's often done,
and whilst no doubt leads to some memory leak, in modern machines may go
unnoticed. The effects could be worse though if the form also holds other
object references, controls like frames and multipage on the form might also
add to the leak.

Regards,
Peter T

 

[Alok]

Hi WHA,

I have been coding VBA for a substantial amount of time and large
projects and have only rarely faced memory leakage problems.

Out of Memory has mainly been because of an unterminated recursive
call. This could be ruled out in your case because you are getting the
error when you are in IDE and are not running any code. Errors in IDE
are mainly because of corruption in the compiled code. This is best
handled by removing each of your modules, class modules and forms to
text files (using the save option when prompted). Then save the
workbook. Close workbook and reopen the workbook and then import each
of the modules, class modules and forms from text files where you
saved them. (This ofcourse can be done more easily by selecting
multiple text files in file explorer and dragging and dropping them in
the VB IDE at one go). There are automated tools out on the internet
for doing this cleanup.

Alok

 

[WHA]

Well, technically I was still unloading the second form and then
unloading the first ... the difference was that I was doing both
unloads from within the second form. But your points are well taken.
Thanks again for sticking with this thread --- WHA

 

[WHA]

Oh! That is interesting. In fact, 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.

Thanks -- WHA

 

[Peter T]

If you have not experienced problems since 'cleaning' the project maybe that
was it, assuming you've not also changed the code.

then hand the tool over to
users when it's ready.

By "tool" do you mean give Rob Bovey's CodeCleaner to users so they can
maintain your app in good condition. I hope not !

Does code corruption ONLY occur when I am working
in the development environment

My initial thought was a firm yes, corruption of the kind that can be
cleaned by export/import of code modules only occurs while developing in the
IDE. However on reflection I'm not so sure, it's a good question. One way to
check would be see if the file size increases after running code and saving,
without the VBE open and not making any other changes to the wb. Would first
need to ensure the code was fully compiled, either with Debug - compile or
by ensuring some code from every module, incl sheet and thisworkbook module,
has been run (on which point note it is normal for file size to increase
after 'cleaning' and then compiling). If you find corruption does reoccur,
sending the CodeCleaner to your users to patch up is not the solution!

It's not inconceivable your project became corrupted during development due
to not releasing those forms correctly, or something related.

Regards,
Peter T

 

[WHA]

Nope -- I only meant the tool I'm building. Compiling and then testing
to see whether file size changes is a good idea -- will check it out.

WHA