On 20 Sep, 15:16, FCS wrote:
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:
ToolsOptionsCalculation

James