Re: Infinite loop with ExcelAsyncUtil.Run and TODAY()

[Govert van Drimmelen]

Hi Vincent,

Indeed, volatile and async don't mix well. Here is an earlier
discussion, with no good resolution but some suggestions:
https://groups.google.com/group/exceldna/browse_frm/thread/115e164e1f2c3cb1

Basically the end of the async causes a recalc, as it has to. This
recalc in turn triggers a recalc of the volatile cell which is an
input to your function. So the function (now no longer an active RTD
function) restarts the async calculation. With multiple instances that
are not finishing at the same time, you get the ongoing recalculation
loop.

What to do?
Either get rid of the volatile dependency, or change your function to
be more robust. For example you can cache the last call to the
function, and if the parameters are the same as before, just return
the value synchronously.

So the behaviour you see is 'by design' and consistent with how I
understand the Excel-DNA async implementation would work.

Regards,
Govert


On Dec 6, 9:32 pm, Vincent Bouret <vincent.bou...@gmail.com> wrote:
> I saw in debug that Excel was indeed calling the UDF repeatedly and I found
> this documentation on Microsoft website:
>
> Excel supports the concept of a volatile function, that is, one whose value
> cannot be assumed to be the same from one moment to the next even if none
> of its arguments (if it takes any) has changed. Excel reevaluates cells
> that contain volatile functions, together with all dependents, every time
> that it recalculates. For this reason, too much reliance on volatile
> functions can make recalculation times slow. Use them sparingly.
>
> The following Excel functions are volatile:
>
>    - *NOW*
>    - *TODAY*
>    - *RAND*
>    - *OFFSET*
>    - *INDIRECT*
>    - *INFO* (depending on its arguments)
>    - *CELL* (depending on its arguments)
>
> Both the VBA and C API support ways to inform Excel that a user-defined
> function (UDF) should be handled as volatile. By using VBA, the UDF is
> declared as volatile as follows.
> And just found out the other workbook having problem is using OFFSET.
>
> Vincent
>
>
>
>
>
>
>
> On Thursday, 6 December 2012 14:09:17 UTC-5, Vincent Bouret wrote:
>
> > Using 0.30 Beta (Changeset ), I sometimes have infinite calculation loop
> > problem when using ExcelAsyncUtil.Run().
>
> > It happens for very complex workbooks for which I can't point out the
> > exact issue, but I was able to reproduce the issue with a very simple
> > workbook.
>
> > I have 4 UDF calls (same function) with different string parameters and
> > two date parameters, one of the date parameters a cell containing =TODAY()-1
>
> > =MyUDF("param1";"param2";$B$1;$C$1)
> > =MyUDF("param1b";"param2b";$B$1;$C$1)
> > =MyUDF("param1c";"param2c";$B$1;$C$1)
> > =MyUDF("param1d";"param2d";$B$1;$C$1)
> > B1 value is 2012-01-01
> > C1 value is =TODAY()-1
>
> > When refreshing the workbook, the first three appears in the #Getting Data
> > state (waiting for the final RTD output) and the last one is fine. A second
> > later, the three first rows are filled with value and the fourth appears in
> > the #Getting Data state. And it goes back and forth in an infinite loop
> > manner.
>
> > The sync function is called with ExcelAsyncUtil.Run() and I see the
> > parameters differenciation in the second parameters.
>
> > I understand =TODAY() might be declared volatile in Excel, because it
> > could change any second, but the RTD server is receiving a call-string of
> > constant data so it should not fire more events, unless Excel asks for
> > recalculation based on the volatility of TODAY.
>
> > How do you think I could fix this?
>
> > Thanks,
>
> > Vincent

[Valentina]

Hi Govert,

I have the same problem, but it 's difficult to resolve.

Do you have code example of caching result in UDF with datetime parameter?

Thank you

 

Valentina

[Govert van Drimmelen]

Hi Valentina,

There's a small example of how you might do the caching here: http://excel-dna.net/2013/04/02/caching-and-asynchronous-excel-udfs/

Suppose your function takes a share code and a date time. You'd combine these values into a key for the cache entry, and only do the async call if the given key is not found in the cache.

You might also set some cache expiration time if your underlying value might change.

Regards,

Govert

[Valentina]

 

I used your example with ExcelAsyncUtil.QueueAsMacro call and it works, thank you very much!!

 

Regards,

Valentina

[Henrik Bergström]

My 2 cent...

Quandl has found a way to stop refresh.. Credit to them.

1. Mark the UDF as a Macro and Volatile to begin with.

2. When UDF runs immediately mark it as non-volatile

3. When outputting the data run this in a Queued macro function so that it does not influence the running calculation thread.

 

https://github.com/quandl/quandl-excel-windows/blob/9410315e2a4ebd7d5c707e6f2f7243d8ee1b6af4/Quandl.Excel.UDF.Functions/UserDefinedFunctions/Datatable.cs

Br

Henrik