Formulae referencing volatile cells are also effectively volatile, so
referencing a single volatile cell wont help.

I would suggest you use Named Ranges and INDEX rather than INDIRECT and
OFFSET.

You may be able to use Sheet.calculate (Shift-F9) to calculate a particular
sheet.

regards
Charles
______________________
Decision Models
FastExcel 2.3 now available
Name Manager 4.0 now available
www.DecisionModels.com

"tskogstrom" wrote in message
oups.com...
Hi,

I had to have a lot of arrays on several sheets and they all had to
have volatile OFFSET and INDIRECT functions. Since arrays always have
to calculate each cell etc, there will occure some calculating time

- Is it possible with VBA to make the cells in the active sheet
volatile and the rest sheets non-volatile? Then every time you have
ws_activate event, you could calculate that specific sheet instead.

Othervice, the user will not see the SUM results from changing one cell
and become unsure.


- Another way could be to put the volatile formulas in ONE cell and
referre to that cell ... , but this is array formulas of 1 x 500 cells
( x10 columns on the sheet). How could that be made? (This Q might be
passed to .excel.functions newsgroup, I guess but I'll give it a try
here now.) Maybe

Happy to suggestions
/Tskogstrom

If interested: Background to Offset, indirect and arrays:
The reason I need OFFSET and INDIRECT and arrays is because I have an
input sheet were users had to be able to do cut, paste and drag- and-
drop. This would ruin the reference links, but now I have arrays
starting one cell above and end one cell under the unprotected area.
They can even insert and delete rows and the array is intact.

I have tested other solutions, like have code to restore cell
references and copy-paste formulas etc, but that has huge drawbacks and
I decided to use this instead. Now I use less VBA and more cell
formulas and made it generally a lot faster, except this thing ...