Hi,

I have a range of an array entered (Ctrl+Shift+Enter) formula that is
calculated using a function in VBA.

The value of each cell depends on inputs from other worksheets. I find
that if I change these inputs, the array entered cells will not
automatically recalculate (I have the options set to automatic
recalculation). In fact, even if I hit F9 or go to Tools Options
Calculation Calc Sheet nothing happens.

The only way to recalculate the cells is to re-highlight the range and
hit Ctrl+Shift+Enter again.

I can rewrite the function slightly so that a regular enter will
suffice. However, given the computational complexity of the function,
and the size of the range, this is not a feasible solution.

Would appreciate any thoughts.
Schiz

Hi Schiz,

CTRL + ALT + F9 forces the recalculation?

By the way, my custom array formulas recalculate while changing any
value of precedent cells (on other worksheets), even if not volatile.

But maybe setting it to volatile could help.

Regards,
Ivan

Make sure that all the input cells are passed to the function as parameters
rather than being referenced from within the function.

Charles
______________________
Decision Models
FastExcel 2.2 Beta now available
www.DecisionModels.com

"Schizoid Man" wrote in message
...
Hi,

I have a range of an array entered (Ctrl+Shift+Enter) formula that is
calculated using a function in VBA.

The value of each cell depends on inputs from other worksheets. I find
that if I change these inputs, the array entered cells will not
automatically recalculate (I have the options set to automatic
recalculation). In fact, even if I hit F9 or go to Tools Options
Calculation Calc Sheet nothing happens.

The only way to recalculate the cells is to re-highlight the range and hit
Ctrl+Shift+Enter again.

I can rewrite the function slightly so that a regular enter will suffice.
However, given the computational complexity of the function, and the size
of the range, this is not a feasible solution.

Would appreciate any thoughts.
Schiz

Charles Williams wrote:
Make sure that all the input cells are passed to the function as parameters
rather than being referenced from within the function.

Charles,

That is exactly the problem. The amount of inputs are quite large, and
fairly unmanageable.

So I do have a couple of inputs passed through to the function, but the
vast majority of the inputs are read in the body of the function itself.

Is there no way to force the calcuation other than this?

Well, you can add Application.Volatile to force the UDF to always be
recalculated, but thats horribly inefficient.

Whats wrong with using Ranges as inputs?

Charles
______________________
Decision Models
FastExcel 2.2 Beta now available
www.DecisionModels.com

"Schizoid Man" wrote in message
...
Charles Williams wrote:
Make sure that all the input cells are passed to the function as
parameters rather than being referenced from within the function.

Charles,

That is exactly the problem. The amount of inputs are quite large, and
fairly unmanageable.

So I do have a couple of inputs passed through to the function, but the
vast majority of the inputs are read in the body of the function itself.

Is there no way to force the calcuation other than this?

Charles Williams wrote:
Well, you can add Application.Volatile to force the UDF to always be
recalculated, but thats horribly inefficient.

Whats wrong with using Ranges as inputs?

Charles

Thanks, Charles. I did just that. Making a volatile call is horribly
inefficient - sort of like bringing a rocket launcher to a knife fight.

I just rewrote the function so as to pass all the inputs as parameters,
got rid of the excess ranges that were reading values from various
sheets, etc.

I'm good to go.