See also http://www.dailydoseofexcel.com/arch...e-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.Line s(1,application.VBE.ActiveCodePane.CodeModule.Coun tOfLines))/
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.

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" wrote in message
...
See also
http://www.dailydoseofexcel.com/arch...e-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.Line s(1,application.VBE.Active
CodePane.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.

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.

On Nov 30, 5:49 am, "Peter T" <peter_t@discussions wrote:
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" wrote in message

... See also

http://www.dailydoseofexcel.com/arch...e-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.Line s(1,application.VBE.Active
CodePane.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.

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

"WHA" wrote in message
...
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.

On Nov 30, 5:49 am, "Peter T" <peter_t@discussions wrote:
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" wrote in message


...
See also

http://www.dailydoseofexcel.com/arch...e-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.Line s(1,application.VBE.Active
CodePane.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.

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

On Nov 30, 9:56 am, "Peter T" <peter_t@discussions wrote:
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

"WHA" wrote in message

...

Thank you for the reply -- the Edit/Delete/Create functions are for
records that are stored in a separateExcelworkbook ("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-memoryerror.

I have more info athttp://tinyurl.com/374w7w("Memoryproblem: Out of
Memoryand cannot save") on possible sources of thememoryleak.

On Nov 30, 5:49 am, "Peter T" <peter_t@discussions wrote:
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 tomemoryleaks 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" wrote in message

...
See also

http://www.dailydoseofexcel.com/arch...e-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.Line s(1,application.VBE.Active

CodePane.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 theUserform?

Thanks in advance, WHA

Background: I am building a VBA-based tool inExcel2003 on Windows
XP. I'm having some kind ofmemoryleak 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.

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" wrote in message
...
(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."

On Nov 30, 9:56 am, "Peter T" <peter_t@discussions wrote:
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

"WHA" wrote in message


...

Thank you for the reply -- the Edit/Delete/Create functions are for
records that are stored in a separateExcelworkbook ("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-memoryerror.

I have more info athttp://tinyurl.com/374w7w("Memoryproblem: Out of
Memoryand cannot save") on possible sources of thememoryleak.

On Nov 30, 5:49 am, "Peter T" <peter_t@discussions wrote:
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 tomemoryleaks 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" wrote in message


...
See also


http://www.dailydoseofexcel.com/arch...e-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.Line s(1,application.VBE.Active

CodePane.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 theUserform?

Thanks in advance, WHA

Background: I am building a VBA-based tool inExcel2003 on Windows
XP. I'm having some kind ofmemoryleak 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.