range.calculation with UDF not working when calculation is set to automatic

[Brian Murphy]

Hello everyone,

I have run into an odd behavior of UDF's that I don't understand. This applies to excel 97, 2000 and 2002.

I have some cell formulas that call a UDF.

I have an addin that runs a macro that fetches values from these cells. This addin macro does some iterating and the values returned from these cell formulas are different each iteration. Right before the addin macro fetches these values it performs a Range.Calculate to make sure the cell formulas are up to date.

This all works fine when Application.Calculation is set manual, but oddly enough it does not work when set to automatic.

When set to automatic, it looks like the UDF is not getting called either automatically or by my Range.Calculate statement.

Is this a known behavior of UDF functions?

Thanks,

Brian Murphy
Austin, Texas

 

[Charles Williams]

Hi Brian,

Yes it does seem to work this way. Unexpected but very clever:

If the iteration loop changes the precedent of the UDF then it gets
calculated by the automatic calculation before you get to the
range.calculate, so the UDF is always up-to-date anyway.
Also if you have a volatile function somewhere in the workbook it will be
recalculated on each iteration of range calculate even when the volatile
function is not being referenced by the range calculate and the UDF which is
referenced by the range calculate is not recalculated.

If the UDF is flagged as volatile then it gets calculated once per iteration
in either automatic mode or manual mode.

So as far as I can see the UDF should always contain the "correct" value.

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

Hello everyone,

I have run into an odd behavior of UDF's that I don't understand. This
applies to excel 97, 2000 and 2002.

I have some cell formulas that call a UDF.

I have an addin that runs a macro that fetches values from these cells.
This addin macro does some iterating and the values returned from these cell
formulas are different each iteration. Right before the addin macro fetches
these values it performs a Range.Calculate to make sure the cell formulas
are up to date.

This all works fine when Application.Calculation is set manual, but oddly
enough it does not work when set to automatic.

When set to automatic, it looks like the UDF is not getting called either
automatically or by my Range.Calculate statement.

Is this a known behavior of UDF functions?

Thanks,

Brian Murphy
Austin, Texas

 

[Brian Murphy]

Hello Charles, thank you for the reply.

I'm not using Volatile anywhere in this situation.

There's a twist to this I didn't point out in my first post.

There is nothing in the workbook that gets changed in the "precedence" of
the cell formula. Which tells me that Excel's recalculation engine will
figure it doesn't need to be called. When the UDF runs, it contains calls
to some other functions in the same addin that does the iterating.

By the way, the values returned to the cells by the UDF are only used by the
addin while it's iterating. The values displayed at other times are not
important.

When in Manual mode, the UDF gets called by pressing ctrl-alt-F9, but not
any other F9 combination. It also gets called by going directly to a cell
and pressing F2 and Enter. When the addin is running things, each execution
of the Range.Calculate statement causes the UDF to get called.

When in Automatic mode, behavior is the same as Manual, except the
Range.Calculate statement does not cause the UDF to get called. I put a
MsgBox statement in the UDF, and I get it on every iteration when in Manual
mode, but don't get it in Automatic mode.

It seems odd to me that specifically calling a Range.Execute statement in a
macro won't force Excel to recalculate the cells in that range when Excel is
in Automatic mode. This is why I posted my question. I guess when Excel is
in Automatic mode, it thinks the range is already up to date, and so doesn't
take any action when Range.Calculate is run. But in my particular
application, the UDF does in fact need to be recalculated because of its
interaction with the addin.

I hope this made some sense.

Here's a little background. My addin performs extensive numerical analysis.
The UDF called from a worksheet cell is how I enable a user to write their
own numerical procedures that my addin can "call" while its working. This
is an enormously versatile and powerful technique for my particular
application. I use Range.Calculate to run their procedures as needed from
within my addin.

Cheers,

Brian

 

[Brian Murphy]

Hello Charles,

If my understanding of the calculation situation is correct, I should be
able to add some extra logic inside my addin to force the UDF to get called
even when Excel is in Automatic mode. Either use a statement different than
Range.Calculate that will work (but not Sendkeys), or change the mode to
manual, do the work, and change it back.

Brian

 

[Charles Williams]

Hi Brian,

Yes I think you are correct:

I can see these alternatives:
- somehow add appropriate precedents to your UDF arguments.
- put Application.volatile in your UDFs
- use the change = to = trick to force evaluation of the UDFs
- use Worksheet.Evaluate to evaluate the UDFs and then somehow return the
values
- use your addin to check calculation, and if automatic then switch to
manual, iterate and then switch back to automatic.
- use calculatefull if Excel>97, and either sendkeys or
worksheet.enablecalculation to force full calculation with excel97.

Personally I think the switch to manual approach is probably the way to go,
although you may have to trap the UDFs being unneccessarily called again
when you switch back to automatic.


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

 

[Brian Murphy]

Hello Charles,

I will give these a try and see which I like best. I also expect the winner
to be shuffling the .Calculation property.

Thanks for your help.

Cheers,

Brian