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 Excel97, 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" wrote in message
...
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" wrote in message
...
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

"Brian Murphy" wrote in message
...
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