Preventing a function being called if argument is unchanged
 

I have a function in a cell triggered on the value returned by a DDE
link in another cell.

e.g. cell A1 contains =function1(A2) and cell A2 contains
=ADVFN|NYSE_CAT!CUR

function1 is triggered each time the DDE link updates regardless of
whether the result returned has changed or not. How do I prevent
function1 being triggered if the returned value has not changed?

Thanks

Preventing a function being called if argument is unchanged
 

Clive wrote...
I have a function in a cell triggered on the value returned by a DDE
link in another cell.

e.g. cell A1 contains =function1(A2) and cell A2 contains
=ADVFN|NYSE_CAT!CUR

function1 is triggered each time the DDE link updates regardless of
whether the result returned has changed or not. How do I prevent
function1 being triggered if the returned value has not changed?

If function1 is a user-defined function which you could modify, then
you could use an approach like


Function function1(arg As Variant) As Variant
Static oldarg As Variant, oldretval As Variant
'
'original variable declarations here
'
If not IsEmpty(oldretval) And oldarg = arg Then
function1 = oldretval
Exit Function
End If
'
'original code here
'
oldarg = x
oldretval = function1
End Function


Excel would still call the function each time A2 refreshes, but it'd
return as quickly as possible. This is the only way to avoid calling
the function without storing the previous value of A2 in a different
cell.

Preventing a function being called if argument is unchanged
 

Thanks Harlan.

The problem is to eliminate the execution of the user defined function
when the value returned by the DDE link is unchanged in order to reduce
the cpu load. The DDE link updates many times per second and for the
majority of these updates the returned value is the same. There are
literally hundreds of these links in my worksheet, each with an
associated udf using the returned value as an argument. I have no
control over the third party DDE interface.

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

Harlan Grove wrote:
Clive wrote...
I have a function in a cell triggered on the value returned by a DDE
link in another cell.

e.g. cell A1 contains =function1(A2) and cell A2 contains
=ADVFN|NYSE_CAT!CUR

function1 is triggered each time the DDE link updates regardless of
whether the result returned has changed or not. How do I prevent
function1 being triggered if the returned value has not changed?

If function1 is a user-defined function which you could modify, then
you could use an approach like


Function function1(arg As Variant) As Variant
Static oldarg As Variant, oldretval As Variant
'
'original variable declarations here
'
If not IsEmpty(oldretval) And oldarg = arg Then
function1 = oldretval
Exit Function
End If
'
'original code here
'
oldarg = x
oldretval = function1
End Function


Excel would still call the function each time A2 refreshes, but it'd
return as quickly as possible. This is the only way to avoid calling
the function without storing the previous value of A2 in a different
cell.

Preventing a function being called if argument is unchanged
 

Clive wrote:
Thanks Harlan.

The problem is to eliminate the execution of the user defined function
when the value returned by the DDE link is unchanged in order to reduce
the cpu load. The DDE link updates many times per second and for the
majority of these updates the returned value is the same. There are
literally hundreds of these links in my worksheet, each with an
associated udf using the returned value as an argument. I have no
control over the third party DDE interface.

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

Harlan Grove wrote:
Clive wrote...
I have a function in a cell triggered on the value returned by a DDE
link in another cell.

e.g. cell A1 contains =function1(A2) and cell A2 contains
=ADVFN|NYSE_CAT!CUR

function1 is triggered each time the DDE link updates regardless of
whether the result returned has changed or not. How do I prevent
function1 being triggered if the returned value has not changed?

If function1 is a user-defined function which you could modify, then
you could use an approach like


Function function1(arg As Variant) As Variant
Static oldarg As Variant, oldretval As Variant
'
'original variable declarations here
'
If not IsEmpty(oldretval) And oldarg = arg Then
function1 = oldretval
Exit Function
End If
'
'original code here
'
oldarg = x
oldretval = function1
End Function


Excel would still call the function each time A2 refreshes, but it'd
return as quickly as possible. This is the only way to avoid calling
the function without storing the previous value of A2 in a different
cell.

Preventing a function being called if argument is unchanged
 

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.

Preventing a function being called if argument is unchanged
 

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.

Preventing a function being called if argument is unchanged
 

Clive wrote:
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.
....

This is very strange. If the cell passed to the UDF evaluates to a
number, then Excel doesn't recalc, even minimally, if the same cell
contents are re-entered. But if the cell evaluates to anything else,
Excel does recalc.

Actually, it gets stranger. With the numeric constant 1 in cell A1, the
formula =A1 in cell B1 and the formula =foo(B1) in cell C1, repreatedly
typing [F2] [Enter] in either of cells A1 or B1 leaves the value in
cell C1 unchanged. Now enter 1 in cell A1 in another worksheet (which
I'll name Test) and change the formula in cell B1 in the original
worksheet to =Test!A1. Repeatedly type [F2] [Enter] in cell B1, and the
formula in cell C1 *does* change each time.

It appears Excel treats numeric constants and formulas involving zero
or more range references WITHIN THE SAME WORKSHEET differently than it
does formulas with references to other worksheets or workbooks. A crude
rule-of-thumb may be that Excel recalcs whenever any formula including
syntactically meaningful exclamation points is entered, whether the
resulting value changes or not. Further, Excel recalcs whenever such
formulas refresh. Since DDE links include syntactically meaningful
exclamation points, Excel recalcs whenever they refresh whether or not
their values change.

In other words, I don't believe you have any chance of achieving the
functionality you describe without using cached previous values.

Preventing a function being called if argument is unchanged
 

Hi Harlan,

Very interesting stuff!

However, I don't fully understand this term...

syntactically meaningful exclamation points

What exactly are they?

Ken Johnson

Preventing a function being called if argument is unchanged
 

Ken Johnson wrote...
....
However, I don't fully understand this term...

syntactically meaningful exclamation points

What exactly are they?

Filenames can contain exclamation points (and single quotes and square
brackets), and text strings delimited by double quotes can also. Those
exclamation points are NOT syntactically meaningful, they're just part
of larger tokens. Exclamation points in worksheet range references,
external references and DDE links, in which exclamation points are
outside of single or double quoted strings, are syntactically
meaningful.

Preventing a function being called if argument is unchanged
 

Thanks for clearing that up for me Harlan.

Ken Johnson