Avoiding a range in recalculation

[Guest]

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?

 

[JE McGimpsey]

One way:

Clear the summary cells when your macro starts, and reinsert the summary
formulae at the end of the macro.

 

[james.billy]

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

 

[Guest]

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,D220")
.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,D220")
.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.