Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a way to speed up Calculation !!!!!!!!!!!!!!!
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a way to speed up Calculation !!!!!!!!!!!!!!!
physical limitations
get more memory and/or faster cpu -- Hope this helps. If it does, please click the Yes button. Thanks in advance for your feedback. Gary Brown "Ayo" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a way to speed up Calculation !!!!!!!!!!!!!!!
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 "Ayo" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a way to speed up Calculation !!!!!!!!!!!!!!!
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 "Ayo" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a way to speed up Calculation !!!!!!!!!!!!!!!
"Ayo" wrote:
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 ----- "Ayo" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculation Speed | Excel Discussion (Misc queries) | |||
Calculation Speed | Excel Worksheet Functions | |||
Calculation speed issue | Excel Discussion (Misc queries) | |||
Calculation Speed | Excel Discussion (Misc queries) | |||
Maximum Calculation Speed | Excel Worksheet Functions |