VBA Exceeding Calculation Resources

[Miraun]

Thanks for the help on the filling to columns... I know it may not be best practice, but I was autofilling some multi-tiered array formula to be used for some graphing down the line...

However, now that I can successfully populate a nice 40-50 array formula instantaneously within the VBA, I've obtained a new error code that I have never seen before!

"Excel Ran out of resources while attempting to calculate one or more formulas. As a result, these formulas cannot be evaluated"

Is there a VBA command I can use to purge the memory after particular pieces of a long macro have executed?
Or perhaps someone have a website that they can refer me to that explains how to better use Subs and modules to assist in excel memory allocation?


The file itself is only 55kb, but it is currently taking up 835,200k in my task manager. Any ideas?

I'd like to think my code isn't THAT poorly written

[TMS]

I'd like to think my code isn't THAT poorly written

Hard to comment without seeing the code or the workbook.

Regards

[Miraun]

Yeah, you're right on that TM...

I've done some research on it myself, and noticed a few things that I could have done better...

First and foremost, I took the lazy way of performing lookups and references and used entire columns rather than just the usedrange of the columns. An additional step that could have saved tons of calculation time... I was under the impression that in 2007, if you used a column reference, it would only perform that calculation on the usedrange within that reference... apparently not!

I didn't properly divide my macros by modules... so it was running error-proofing on all subs, regardless of which one was being run.

I also didn't turn Application.Calculation to Manual from automatic.

I'll go ahead and mark it closed, make those changes, and then if I have more problems, I'll post some sections of the code. The problem is that the data is pretty confidential, and it's difficult to understand the code without the data.

[TMS]

As you wish.

It is still possible to offer some advice on the code, although it can be difficult to test and check that the original expectation is still met.

Regards