I get the "out of memory" message when I execute my macro. It is too long to post but are there hints to better control the memory usage in Excel 2007? Getting rid of objects or something like this. I don't know how to do this.
best, U
2
-
1Without code, it will be difficult to understand why that should be the case. In general, don't have large in memory objects, release variables you are done using. Is there a specific line on which this error occurs?shahkalpesh– shahkalpesh2011-06-30 08:01:20 +00:00Commented Jun 30, 2011 at 8:01
-
It used to occur after finishing. Basically I have a lot of subroutines to format and populate a worksheet depending on criteria I define in a userform. I call all these subroutines in my "main" subroutine.user366121– user3661212011-06-30 08:23:44 +00:00Commented Jun 30, 2011 at 8:23
Add a comment
|
2 Answers
Excel is notorious for this message and chances are, it has nothing to do with your computer's memory. I've had top of the line computers present this message with a 100kb excel program. The key COULD BE your userform and how certain parts get called. Best bet would be to run each event you have listed within your userform and see which one is the culprit. Things to note: Call
userform1.show vbmodeless
after all the textboxes, and other controls are set.
That sometimes works.
Comments
...Basically I have a lot of subroutines to format and populate a worksheet depending on criteria I define in a userform.
I agree with @shahkalpesh comment. However, if you're looking for a strategy to help pinpoint the problem, you could try putting message boxes after you call each sub-routine from your main routine and observe excel's memory in the Task Manager.
Perhaps this will help you single out the offending code. Once found, you could post the code in here to resolve your issue.
3 Comments
Jean-François Corbett
"putting message boxes" Or, step through the program in debug mode.
ray
@Jean-François Corbett have you ever tried to step through thousands of lines with of code over several subroutines? Based off the comment "too long to post", I'm guessing "too long to step through" as well. A messagebox can help pinpoint the issue to a particular procedure w/o incessantly pressing F8. Breakpoints could be set on each routine as well...I just love messageboxes :P
Jean-François Corbett
Answer is yes, I have. And that's right, breakpoints. And then whatever Function key you feel is appropriate.