c#: ExcelDNA resize array asynchronous mode

[DanyM]

hello there

@Govert van Drimmelen 

I tried the solution posted here  to try to resize dynamically excel cells. It works fine with this code : Page page = new Page(); WebResponse resp = page.Download(url); List<string> res = page.GetAllFromXpath(resp.content, xpath); object[,] result = new object[1, res.Count]; for (int j = 0; j < res.Count; j++) { result[0, j] = res.ElementAt(j); } return Resizer.Resize(result); but when i try the asyncrohous method, this throw an exeption not handled : return ExcelAsyncUtil.Run("DownloadAsync", url, delegate { Page page = new Page(); WebResponse resp = page.Download(url); List<string> res = page.GetAllFromXpath(resp.content, xpath); object[,] result = new object[1, res.Count]; for (int j = 0; j < res.Count; j++) { result[0, j] = res.ElementAt(j); } return Resizer.ResizeMe(result); }); ExcelDna.Integration.XlCallException' occurred in ExcelDna.Integration.dll but was not handled in user code And here is the line in the Reizer class that throw it: ExcelReference caller = XlCall.Excel(XlCall.xlfCaller) as ExcelReference;

someone can help ?

[Govert van Drimmelen]

Hi Dany,

 

Array functions and the async support don’t work together well, and with the resizer is not expected to work at all.

The Excel RTD functionality that Excel-DNA uses to implement the async functions is not really supported for array functions, and when called with array results works in ways that are not expected by the resizer.

 

The recommended workaround is to split your function into two parts:

• One function that you call from a single cell is an async UDF function that retrieves the data, stores it in an internal data structure in your add-in, and returns some kind of handle to Excel.
• Another function that is not async, takes the handle as input, and returns the array with possible resizing.

 

It’s a bit more inconvenient for the users, as the have to deal with the two functions and the extra cell, but at least it will be reliable.

 

For a full example of this pattern, with some helpers code, see the ACQ add-in by Alex Chirokov here: https://github.com/ratesquant/ACQ

And find the detailed write-up which also discusses this handle pattern here: https://www.codeproject.com/Articles/1097174/Interpolation-in-Excel-using-Excel-DNA

 

-Govert

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.
Visit this group at https://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.