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