Re-Calculation on a single worksheet

[Guest]

Is it possible to set calculations to manual for a specific worksheet only of
a workbook? I've got one tab of a workbook that contains a number of array
functions that pull data from the other tabs - unfortunately they slow the
file tremendously. I would set calculation to manual for the whole file, but
the other tabs have simple formulas that I'd like to leave on automatic
calculation.

 

[Guest]

Shift-F9 calculates only the current worksheet.
HTH,

 

[Charles Williams]

This is what FastExcel calls Mixed-Mode worksheets.

If all the other sheets do not have any links between them or the links are
in a simple linear sequence then you can press Shift-F9 for each of the
other sheets in the right sequence.

A better way is to use the worksheet.enablecalculation property to do this,
using VBA.
set it to False to prevent the worksheet being calculated and set it to True
again to make the worksheet be recalculated if you are in Automatic or next
time you press F9 if you are in manual.

There are some quirks:
- this property is not saved with the workbook, so you have to reset it each
time you open the book.
- when set to False the worksheet will not be calculated, but when you set
it back to true and do a calculation every formula on the sheet will be
calculated (its a Full sheet calculation rather than a sheet recalculation)
- because the property is not saved with the workbook when you save & close
the workbook the next time you open it the sheet will be calculated before
you can reset the property to false!


Charles
______________________
Decision Models
FastExcel 2.1 now available
www.DecisionModels.com