Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Preventing a function being called if argument is unchanged
Thanks for clearing that up for me Harlan. Ken Johnson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Passing a range name as an argument to the Index Function | Excel Discussion (Misc queries) | |||
Need Function Argument | Excel Worksheet Functions | |||
text as an argument of SUM function | Excel Worksheet Functions | |||
mystery function called 'TABLE' | Excel Worksheet Functions | |||
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions |