Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.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? |
#2
Posted to microsoft.public.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. In article , 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Avoiding a range in recalculation
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 |
#4
Posted to microsoft.public.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 " wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Avoiding #N/A | Excel Discussion (Misc queries) | |||
Avoiding Recalculation for a function in automatic mode | Excel Worksheet Functions | |||
AVOIDING DUPLICATES IN A RANGE OF CELLS | Excel Discussion (Misc queries) | |||
Problem with Slow ReCalculation of Dynamic Range Using OFFSET | Excel Worksheet Functions | |||
write one column range to text avoiding final linebreak | Excel Programming |