Avoiding a range in recalculation

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

FCS - 20 Sep 2007 15:16 GMT

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?

Reply to this Message

JE McGimpsey - 20 Sep 2007 15:23 GMT

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
[quoted text clipped - 6 lines]
> How could I restrict the recalculation so that the summary is never
> calculated until the simulation is completed?

Reply to this Message

james.billy@gmail.com - 20 Sep 2007 17:42 GMT

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

Reply to this Message

Tom Ogilvy - 20 Sep 2007 18:22 GMT

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.

Signature

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
[quoted text clipped - 21 lines]
>
> James

Reply to this Message