Tracking down "Out of Memory" error

hi,

I am dealing with a macro that works with a large number of user-defined objects (milestones on a schedule)
recently, "Out of Memory" keeps coming, but I don't know its origin, nor how to solve it

what steps should I follow?

I see that function INFO("memused") is no longer available...

all help appreciated!

You could start by posting your code.

Have you even identified the sub/function it occurs in?

Hi Sixthsense,

I do have a couple of UDF, but they are used just to recalculate a number of cells and then are converted to values, so I asume the problem is not there... am I right?

Kyle123, I really don't know where the problem is, and posting the whole workbook seems a bit excessive. Also, it tends to work fine on the first run, and then the problem appears and will not go away. It even pops-up if I input easy operations on the immediate window such as ?3+4

any clue on the steps I should follow to track it down?

thanks!!

You likely have a long running loop or are holding data in public variables that are increased with each run. Depending on the complexity of your code you may have circular references that can't be garbage collected.

Without seeing any of your code, it's completely impossible for us to help any further as there are many things that may cause this, a lot of them are easy to spot with the right experience.

Hi Sixthsense,

I do have a couple of UDF, but they are used just to recalculate a number of cells and then are converted to values, so I asume the problem is not there... am I right?

Kyle123, I really don't know where the problem is -- it appears after running this macro, but the problem may be in the books it opens

Please Login or Register  to view this content.

any clue on the steps I should follow to track it down?

thanks!!

Your code is hard to follow due to the large reliance on Module level variables rather than parameters and the number of helper functions that obscure what's going on, what's the code for:

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

apologies for the code complexity
that's why I was hoping for some general guidelines to apply to my particular case
anyway, here's the code

Please Login or Register  to view this content.

by the way, the error only shows if VBE is open -- does this ring a bell, or is the default behaviour?

thanks!

Hmm, what I would do is set a lot of break points, step through the code til you get the error. Your style of coding makes is quite difficult to do some of the easier things to try and test, I think you'd have made have made life easier for yourself by:

• Using fewer Public Variables, passing variables as paramaters results in fewer bugs and allows smaller subroutines to be tested as standalone units
• Error handling is not control flow, it's a catch for errors, using it as flow often creates code that's difficult to follow and can have unexpected behaviour
• Using functions where you've used subs would have allowed for a more consistent approach

Does the error message highlight anything, is there a debug button?

Thanks Kyle123

can you clarify a bit more about the use of functions over subs?

then thing is that the error message does not highlight anything -- just "out of memory" and the possibility to click ok.

Have you tried setting breakpoints and stepping through?

Hmm, the point about functions is more personal preference as I think passing by Ref on the whole makes things difficult to follow as it's not easy to see what's going on.

Consider:

Please Login or Register  to view this content.

Without digging through code, it's not at all easy to see what's being changed, contrast that with:

Please Login or Register  to view this content.

Nothing is edited in the function, rather a new array is returned - it makes the code much easier to follow.

FWIW you could make your code a hell of a lot more efficient by using a collection/dictionary. All this, would be a simple single function:

Please Login or Register  to view this content.

the error today seems to have vanished --
I added sentences to set variables to nothing and erase arrays
also, the source workbook is now open in a macro-disabled instance of excel, so there are less things potentially causing the problem.

with arrays I had some trouble in the past and finally settled for this approach which at least didn't cause errors.

in any case -- I'll keep in mind your comments and apply them wherever possible.

thanks for your time!

No problem at all, erasing arrays and setting variables to nothing is normally a waste of time since they get released when they go out of scope anyway


Sent from my iPhone using Tapatalk