Array function comments

[Laurence Lombard]

Array functions work well, but the problem is they can slow down a workbook
if there a a few complicated ones in it (even if they are in a sheet
different to the one you are in). It is a pity that there is not a refresh
option similar to a pivot table. In my case array functions are mostly used
in some form of summary or report, and they do not need to be continually
updated.

One way to work around it is to put the sheet with array functions in
another workbook and only open that when needed.

Any comments from the newsgroup?

Laurence Lombard

 

[Charles Williams]

Sounds like what you want is the ability to have different calculation modes
at worksheet level.
The way we have done it is to use VBA to control the .enablecalculation
property of the worksheets.

There are also often faster (but sometimes more complex) alternatives to
summary and reporting array formulae,
see http://www.DecisionModels.com/optspeedj.htm and associated pages

or you could convert the formulae to values using copy paste, and then
convert them back to formulae again when you want to calculate.

or your workaround of using a separate workbook also works.

regards
Charles
______________________
Decision Models
The Excel Calculation Site.
www.DecisionModels.com