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 ...

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 ...

Hi Charles,
I saw your reference to FastExcel - I actuallly bought it last year and
can recommend it to anyone with this kind of preformance interest! This
version 2.3 - is that a free update to us who already bought it (v2.1)
and does it add any features I could need?

SHEET.CALCULATE
Do you have any experiences to have manual caclulation and run
sheet.calculate on ws_change event and ws_activate? Will the user
see/feel it is as usual, or will there be any differences?

NAMED RANGES
Regarding Named Ranges: If I use OFFSET in Named Ranges, will that
become non-volatile? And can I (how) can I make array formulas just by
add "{" signs?

ARRAYS
If I have a formula like this:
={IF(ROW()<5,$A$1:$A$200*$B$1:$B$200,"")}

- Will that make 4 calculations or 200? Even as a Named Range? (Would a
Named Range make any difference in that particulat case?)

If I have a long megaformula instead of "A1:A200*B1:B200", will that
bother more than 4 cells?


Kind regards,
Tskogstrom

Charles Williams skrev:

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 ...

Hi Tskogstrom,

Thank you for the FastExcel recommendation.
The update to the latest build is free to anyone who has a license for
either FastExcel version 1 or FastExcel Version 2.
Just download it from the website, Uninstall the old version and reinstall
the new one. You will need your FastExcel license code.
See http://www.decisionmodels.com/FxlV2WhatsNew.htm for details of the
changes in the latest build and version.

Sounds like you should look at using FastExcel's extended calculation modes,
in particular Mixed Calculation mode, which allows you to specify the
calculation mode for individual worksheets.

- OFFSET in named ranges will make them volatile. You can refer to a
calculated subset of a named range using INDEX, which is not volatile, and
you can make a formula decide which of up to 30 Named Ranges to use using
CHOOSE, which is also non-volatile.

- Formulae in Named Ranges are always evaluated as if they were array
formulae.

- ={IF(ROW()<5,$A$1:$A$200*$B$1:$B$200,"")}

This formula will do 200 multiplications for each instance of the formula if
the row that contains the formula is in row 1 through 4.

- Formulae in Named Ranges are evaluated each time they are referenced, so
multiple references to a named range containing a formula are less efficient
than multiple references to a cell containing the same formula.

- Long megaformulae are often (but not always!) less efficient than
splitting formulae up into different cells.

You may find this article I wrote on improving performance useful: although
it talks about Excel 2007 about 90% is applicable to earlier Excel versions
and it identifies the parts thgat only apply to Excel 2007.
http://msdn2.microsoft.com/en-us/library/aa730921.aspx

There is some material there that I have not yet updated my website or
FastExcel help with yet

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 Charles,
I saw your reference to FastExcel - I actuallly bought it last year and
can recommend it to anyone with this kind of preformance interest! This
version 2.3 - is that a free update to us who already bought it (v2.1)
and does it add any features I could need?

SHEET.CALCULATE
Do you have any experiences to have manual caclulation and run
sheet.calculate on ws_change event and ws_activate? Will the user
see/feel it is as usual, or will there be any differences?

NAMED RANGES
Regarding Named Ranges: If I use OFFSET in Named Ranges, will that
become non-volatile? And can I (how) can I make array formulas just by
add "{" signs?

ARRAYS
If I have a formula like this:
={IF(ROW()<5,$A$1:$A$200*$B$1:$B$200,"")}

- Will that make 4 calculations or 200? Even as a Named Range? (Would a
Named Range make any difference in that particulat case?)

If I have a long megaformula instead of "A1:A200*B1:B200", will that
bother more than 4 cells?


Kind regards,
Tskogstrom

Charles Williams skrev:

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 ...