Is there a way to speed up Calculation !!!!!!!!!!!!!!!

[Ayo]

I have this line of code in my macro: Wks.Range("G3:GA732").Calculate

This line of code takes over an hour to execute. The range is all
SUMPRODUCT formulae and I have to run the report daily. It takes about 2
hours to run the report. 90% of that is spent executing the "Calculate" code
above.

What I need to know is this, is there a way to speed-up the Calculating
operation or am I stuck with this?
Thanks

 

[Gary Brown]

physical limitations
get more memory and/or faster cpu

 

[Peter T]

Range("G3:GA732"): Are you really saying you've got 129210 formulas with
SumProduct to calculate, and in turn who knows what else in the formula.

Regards,
Peter T

 

[Tom Hutchins]

I have read occasionally that SUMIF is much faster than SUMPRODUCT.
Substituting SUMIF where possible may cut your recalculation time.

This link is all about optimization:
http://www.decisionmodels.com/

Hope this helps,

Hutch

 

[Joe User]

I have this line of code in my macro:
Wks.Range("G3:GA732").Calculate [....]
The range is all SUMPRODUCT formulae [....]
is there a way to speed-up the Calculating

The precise answer is: maybe yes, maybe no.

You don't provide sufficient information for anyone to offer an intelligent
answer.

People can offer wild-ass guesses. But without knowing anything about those
formulas, much less the design of the entire workbook, such WAGs are only
worth the paper they are written on. (Zero.)

(But people get lucky sometimes.)

Simply calculating 129,210 SUMPRODUCT formulas is not necessarily a
performance killer.

For example, when I populate G3:GA732 in a virgin workbook with the formula
=SUMPRODUCT(($A$3=1)*($B$3=1)), the .Calculate statement takes only about 0.6
sec on my computer. (If A3 and/or B3 is modified beforehand in manual
calculation mode. YMMV.)

So obviously, the complexity of the formulas in those cells and indeed the
entire workbook is a necessary factor to consider.

You neglect to say what calculation mode you are in when the .Calculate
statement is executed.

Presumably you are in manual mode. But if you are not, setting
Application.Calculation = xlCalculationManual might speed things up. Of
course, that suggestion is viable only if you do not depend on side-effects
when calculating G3:GA732.

Also, setting Application.ScreenUpdating = False and
Application.EnableEvents = False might improve calculation performance,
depending on circumstances.

I suspect major performance improvements depend more on your Excel workbook
design than on the macro implementation.

But those are WAGs -- just as valuable (not!) as any other WAG.

This line of code takes over an hour to execute.
[....] It takes about 2 hours to run the report. 90%
of that is spent executing the "Calculate" code

That description is a little suspicious. It is true that 1.8 hours (90% of
2) is "over an hour". But if that's what you meant, I'm surprised you did
not say "nearly 2 hours".

In any case, it is unclear whether the .Calculate time ("over an hour") is
the time for one execution of the statement, or if "over an hour" is the
cumulative time for multiple executions of the statement.


----- original message -----