|
|
Avoiding a range in recalculation |
|
Author |
Message |
JDomey
|
Posted: Thu Sep 20 07:23:18 PDT 2007 |
Top |
Excel Programming >> Avoiding a range in recalculation
I have a fairly large Excel spreadsheet with a small VBA program that
conducts a simulation using the spreadsheets as a calculation engine. A
number of results are obtained from each simulation run which are stored in a
table. The sheet includes a result summary area which summarises data
extracted from the results in accordance with certain criteria using
sumproduct functions. The table is large >40 thousand rows and I don't want
the summary to be calculated every time that a result is added to the table
as it results in an unacceptable reduction in speed (from seconds to hours!).
How could I restrict the recalculation so that the summary is never
calculated until the simulation is completed?
Excel225
|
|
|
|
|
JE
|
Posted: Thu Sep 20 07:23:18 PDT 2007 |
Top |
Excel Programming >> Avoiding a range in recalculation
One way:
Clear the summary cells when your macro starts, and reinsert the summary
formulae at the end of the macro.
> I have a fairly large Excel spreadsheet with a small VBA program that
> conducts a simulation using the spreadsheets as a calculation engine. A
> number of results are obtained from each simulation run which are stored in a
> table. The sheet includes a result summary area which summarises data
> extracted from the results in accordance with certain criteria using
> sumproduct functions. The table is large >40 thousand rows and I don't want
> the summary to be calculated every time that a result is added to the table
> as it results in an unacceptable reduction in speed (from seconds to hours!).
> How could I restrict the recalculation so that the summary is never
> calculated until the simulation is completed?
|
|
|
|
|
james
|
Posted: Thu Sep 20 09:42:10 PDT 2007 |
Top |
Excel Programming >> Avoiding a range in recalculation
> I have a fairly large Excel spreadsheet with a small VBA program that
> conducts a simulation using the spreadsheets as a calculation engine. A
> number of results are obtained from each simulation run which are stored in a
> table. The sheet includes a result summary area which summarises data
> extracted from the results in accordance with certain criteria using
> sumproduct functions. The table is large >40 thousand rows and I don't want
> the summary to be calculated every time that a result is added to the table
> as it results in an unacceptable reduction in speed (from seconds to hours!).
> How could I restrict the recalculation so that the summary is never
> calculated until the simulation is completed?
Could you not switch off calculation?
In Code:
Application.Calculation = xlManual
Then at the end of the simulation:
Application.Calculation = xlAutomatic
Or a manual approach:
Tools>Options>Calculation
James
|
|
|
|
|
TomOgilvy
|
Posted: Thu Sep 20 10:22:01 PDT 2007 |
Top |
Excel Programming >> Avoiding a range in recalculation
Can we assume that part of the simulation is the calculation of formulas in
Excel and turning off calculation would not be acceptable.
then
Sub Simulation()
With Worksheets.Summary("Range("B2:B20,D2:D20")
.Replace What:="=", _
Replacement:="ZZ=", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False
End With
' code that does the simulation and writes the table
With Worksheets.Summary("Range("B2:B20,D2:D20")
.Replace What:="ZZ=", _
Replacement:="=", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False
End With
End Sub
This converts your formulas to text strings so they don't calculate, then
changes them back to formulas. Modify the range to match the cells you want
suppressed.
--
Regards,
Tom Ogilvy
> > I have a fairly large Excel spreadsheet with a small VBA program that
> > conducts a simulation using the spreadsheets as a calculation engine. A
> > number of results are obtained from each simulation run which are stored in a
> > table. The sheet includes a result summary area which summarises data
> > extracted from the results in accordance with certain criteria using
> > sumproduct functions. The table is large >40 thousand rows and I don't want
> > the summary to be calculated every time that a result is added to the table
> > as it results in an unacceptable reduction in speed (from seconds to hours!).
> > How could I restrict the recalculation so that the summary is never
> > calculated until the simulation is completed?
>
> Could you not switch off calculation?
>
> In Code:
>
> Application.Calculation = xlManual
>
> Then at the end of the simulation:
>
> Application.Calculation = xlAutomatic
>
> Or a manual approach:
> Tools>Options>Calculation
>
> James
>
>
|
|
|
|
|
|
|