Recalculating async UDFs

May 27, 2014 at 3:12 PM
Is there any way to clear the cached async UDF results?

I have a class of async UDFs where the current time is always an implied extra parameter and I'd like the user to be able to force the recalculation of these (but not use a RTD server to constantly recalculate as that would just be a waste of backend resources).

Thanks.
May 27, 2014 at 3:26 PM
I looked into this further, and I see different behavior between my array UDFs and my single cell UDFs.

The array UDFs will not recalculate on Control-Alt-F9 and return a cached result.

The single cell UDFs do recalculate on Control-Alt-F9 and call into my backend services.

Not sure what the difference is... they both end up calling:

return ExcelAsyncUtil.Run(functionName, args, handle => ....);

Could the difference be that in the array case args contains an embedded object[] whereas in the single case it does not? I could try flattening the args array.
May 27, 2014 at 3:32 PM
Flattening the args did not change the behavior....there seems to be some underlying difference with the array vs non-array for async UDF recalculation.
Coordinator
May 27, 2014 at 5:04 PM
Hi,

Async functions (actually the underlying RTD mechanism) called as array formulas are problematic. This issue basically raised the same problem: https://exceldna.codeplex.com/workitem/9324

I have since investigated, and Excel's RTD behaviour when called from an array formula is strange. I believe I have found a reasonable workaround, and have checked in a new update - you can download from here: https://exceldna.codeplex.com/SourceControl/latest

The updated version has not been tested with different Excel versions, so if you do get a chance to try it, please report which Excel version you're using and whether it worked for you or not.

An alternative that might be safer and works with the Excel-DNA 0.32 release is to have the async function return a 'handle' of some sort to a single cell, and then have another function which takes the handle, and returns the array from some internal dictionary. The inconvenience is that you need the extra cell and formula.

Anyway, you're not crazy, you have run into a real issue :-)

-Govert
May 30, 2014 at 8:31 PM
Thanks for the speedy reply. I will test out those changes as soon as possible and get back to you. We use Excel 2010 primarily here, with a few people having already migrated to 2013. I will do my best to try it out on both.
Jun 6, 2014 at 6:37 PM
I upgraded to the latest Excel-DNA and am still seeing the same behavior. Asyn UDFs that return an array do not recalculate, those that return a scalar will.
Jun 6, 2014 at 6:39 PM
This is with Excel 14.0.7015.1000 (32-bit)
Coordinator
Jun 6, 2014 at 6:41 PM
Is this with the released Excel-DNA version 0.32, or the more recent source update at https://exceldna.codeplex.com/SourceControl/latest ?
Jun 6, 2014 at 8:31 PM
I used the DLLs in the package from the download link on that page....the ExcelDna.Integration.dll is version 0.33.5262.38998
Coordinator
Jun 6, 2014 at 8:42 PM
Are you sure you used the .xll from that download too?
Jun 6, 2014 at 8:49 PM
Ok...I must not have gotten the new XLL file copied in properly. I did that but now it's weirder...

While things will recalculate, it appears that with no other changes on my end, my functions are being evaluated twice for each call (I have logging around the backend service calls).

It also seems to have broken how I was handling rotating the results for if the call was from a vertical selection.
Coordinator
Jun 6, 2014 at 8:57 PM
OK - thanks for your patience...

The RTD array-function wrapper will be called once for the array, then once for every cell in the array. That's pretty much out of our control - Excel just works this way with the combination of array formula and RTD call. It should not have changed with this version, though. The internal function (the delegate that you pass to ExcelAsyncUtil.Run(...)) should be evaluated only once. If you do something that causes the cell to be re-evaluated after the async call is complete, if will of course run again (which it would not have done before this updated version).

I'm not sure what you are doing with the array orientation. Maybe you can paste (or email me) some code that makes the discussion more concrete.

-Govert
Jun 9, 2014 at 2:21 PM
I made as minimal a solution as I could that exhibits the problem. It's fairly straightforward: a single function that does async work, and a ribbon button to open the log window.

I can only get the results to work when called from a row. I can't get it work when called in a column (you can see both of my attempts at "fixing" the result array.

Also, if you run the function once, and then look in the log you will see that the delegate is evaluated twice.

https://drive.google.com/file/d/0ByhHNvwSrsDuZTZxQ3FyUGJoUzQ/edit?usp=sharing <- I think you have to click File->Download to get the zip from that link, let me know if you have any problems.

Thanks,

~Ethan
Coordinator
Jun 9, 2014 at 9:23 PM
Hi Ethan,

Certainly your NewPackForCaller won't work. You need to return a 2D-array, but currently there is a row array going into the first element of another array. It makes no sense.

I don't think it will be easy to implement the row/olumn rearrangement for async array functions.
The reason is that your function will be called once for the array, then once for every cell in the array, as I mentioned above. In addition, the "caller" information is not reliable for an async array function. For some calls (maybe always the first call) the caller is correct, but for the subsequent calls the caller is a single cell.

Maybe with some kind of state tracking, you can know which calls to listen for in order to return the converted (or not converted) result.

I think the combination of (1) an array function, (2) async using RTD and then (3) using the caller information is beyond what Excel gives us reliably, or at least beyond what I have advice for how to deal with. Maybe you should just make it a 2D array function and not fiddle with the orientation.

-Govert
Jun 9, 2014 at 9:41 PM
Govert,

Thanks for the reply. I wrote that really quickly this morning and was unsure if I was supposed to return a 2D array or a jagged array. I went jagged, oops.

I'm willing to let go of the orientation issue and just tell our users that array functions work in rows only.

I'm more concerned with the double delegate execution at this point.

Thanks,

~Ethan


Coordinator
Jun 9, 2014 at 10:16 PM
Edited Jun 9, 2014 at 10:17 PM
Hi Ethan,

OK. I was a bit surprised that your async code worked at all. It uses the ExcelAsyncHandle that I put in place for the native Excel 2010+ async feature.
I didn't remember that you can call this version via ExcalAsyncUtil.Run(...) as well. From your example, it's clear you can, but the delegate being evaluated twice must be a bug somehow related to this.

I suggest you change it to use the normal (or what I think of as the normal) overload of ExcelAsyncUtil.Run, which takes a Func<T> delegate and does not use the ExcelAsyncHandle:
        public static object ExampleAsyncFunction(
            double multiplier,
            double[] values)
        {
            var caller = XlCall.Excel(XlCall.xlfCaller);
            LogDisplay.RecordLine("ExampleAsyncFunction from {0}", caller.ToString());

            return ExcelAsyncUtil.Run("ExampleAsyncFunction", new object[] { multiplier, values }, () =>
            {
                LogDisplay.RecordLine("In delegate");
                Thread.Sleep(TimeSpan.FromSeconds(1));

                return values.Select(x => multiplier * x).Cast<object>().ToArray();
            });
        }
Let me know if that sorts out your delegate double-evaluation issue.

-Govert
Jun 10, 2014 at 1:16 PM
Edited Jun 10, 2014 at 1:17 PM
So I changed which version of the Run method I was using and it solved the multiple execution issue. Additionally, whenever a function returns object[] I am automatically converting it into an object[,] of the form:
Original   
[1, 2, 3, 4, 5]

Modified 
[1, 2, 3, 4, 5
 2, null, null, null, null
 3, null, null, null, null
 4, null, null, null, null
 5, null, null, null, null]
And that seems to work for the calling the function from a column/row issue. A bit of wasted space but I'm not overly concerned about that.

Thanks for the help,

~Ethan