Fascinating! Para 2 of my previous is not incorrect, at least not on
my version of Excel (2003 SP2). Your test code does not increment as
you suggest but does if I change the value in A1. Then read your reply
again and realised that the difference is that my argument is numeric.
Try it yourself with any number in A1 and the counter will not
increment. That's what I'm try to achieve when the DDE link returns.

Regards

Harlan Grove wrote:
Clive wrote...
...
When a udf points to a cell that contains a value the udf isn't called
until the value changes. So if you re-type the same value into the
argument cell then the udf isn't triggered because Excel is smart
enough to realise that nothing has changed. However it seems that
where the argument cell contains any form of expression the result is
not checked to be unchanged and the udf is always called. This is what
I would like to eliminate as the cpu load is at times reaching 100%.
...

Your second paragraph is incorrect. Here's a very simple udf.

Function foo(Optional x As Variant)
Static n As Long
n = n + 1
foo = n
End Function

It returns incremented integer values whenever it's called. Call it
without any argument, and Excel will only call it when the cell
containing formula calling it is updated or when a full recalc occurs.
Call it with a reference to another cell, and Excel will call it
whenever that other cell changes. So enter the text constant foobar in
cell A1 and the formula

=foo(A1)

in cell B1. Now return to cell A1 and repeatedly press [F2] then
[Enter]. The value in cell A1 remains the same, but Excel reevaluates
the formula in B1 each time. You could try to shield the udf call
behind conditional references using IF calls, but Excel will still
connect the entry cell to the cell with the formula calling the udf
unless you spread the reference chain over more than several thousand
cells (in which case Excel will always display Calculate in the status
bar). That'd be an even bigger performance drag.

No spreadsheet I'm aware of provides any mechanism for checking whether
cell values have changed or not when users make new entries in those
cells. ALL entries trigger minimal recalc, and Excel recalcs ALL
formulas referring to any of the cells that just received entries. The
same is true for every other spreadsheet I've used.

The only way to avoid calling udfs is to wrap them inside IF calls with
the conditional 1st argument to IF checking whether the udf needs to be
recalced or not, and that in turn would require event handlers to store
previous values of DDE links as well as previous values of the udf
calls in your situation. However, Change and Calculate event handlers
controlling when the udfs were called could reduce your recalc time and
CPU load.