Automatic recalculation of array functions

[Schizoid Man]

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

[Gary''s Student]

Is the function Volatile?
--
Gary's Student


"Schizoid Man" wrote:

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

[Dave Peterson]

Do you pass the range that changes to the UDF so that excel knows that it has to
recalculate when a cell in that range changes?

Schizoid Man wrote:
>
> 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

--

Dave Peterson

[Schizoid Man]

Gary''s Student wrote:
> Is the function Volatile?

How do I make the function volatile?

[Schizoid Man]

Dave Peterson wrote:
> Do you pass the range that changes to the UDF so that excel knows that it has to
> recalculate when a cell in that range changes?

Actually, I'm not changing any members of the input range, just other
inputs that are not passed to the UDF but are read by the function in
the body of the code.

[Dave Peterson]

If you don't tell the excel when the UDF should recalc by including the ranges
that are used, then excel won't recalculate until a full recalcuation (or when
you force it to reevaluate that formula with the F2|enter technique).

I think you'd be much better off by supplying the ranges that the UDF depends so
that excel knows when to recalculate.

But you could add "application.volatile" to the top of your function:

Option Explicit
function somefunction(whateveryoupas as something) as somethingelse
application.volatile
'rest of function
end function

But the bad news is that cells that depend on your formula may be one
recalculation behind. You'll want to force a full recalc before you trust the
results of the function--and anything that depends on that function.

Schizoid Man wrote:
>
> Dave Peterson wrote:
> > Do you pass the range that changes to the UDF so that excel knows that it has to
> > recalculate when a cell in that range changes?
>
> Actually, I'm not changing any members of the input range, just other
> inputs that are not passed to the UDF but are read by the function in
> the body of the code.

--

Dave Peterson