Returning multiple results to Excel async UDF's using xlAsyncReturn

[Hughes Hilton]

Hi!

We have Excel UDF's which query a database. Currently, they are marked thread safe which does help somewhat with performance. However, I'm looking at optimizing the situation where a user either recalculates a workbook with many UDF's or changes a cell value that is referenced by many UDF's.  I tried making an async version of a UDF using Excel's native async UDF functionality and it seems to work.  When a calculation happens I can wait till all the calculating threads are done and batch up my database query into 1 large query which takes less time than thousands of small queries.  However, then I have to call SetResult thousands of times with the results which seems to slow down Excel a lot. Then I looked at this MSDN documentation:

https://docs.microsoft.com/en-us/office/client-developer/excel/xlasyncreturn

It seems you can callback to Excel with xlAsyncReturn using xltypeMulti and pass arrays of handles and results back.  It doesn't appear that ExcelDna currently supports this functionality though. Am I missing something? It doesn't seem like it would be difficult to implement, but I'm not much of a C programmer. I might take a stab at it, but I currently can't even figure out what the constant value for xltypeMulti should be (it's not defined in ExcelDna). Anyone have any suggestions or a quick implementation I could test out? Anyone know how to find the value of xltypeMulti? Thanks!

[Hughes Hilton]

Well, I found xltypeMulti in the Excel XLL SDK. It's 0x0040 if anyone is interested.  Maybe I'll take a shot at making this call work.

[Hughes Hilton]

And it's apparently already been defined in ExcelDna as XlTypeArray. That deserved a comment for whoever named it something different than the original Excel SDK name I would say. Anyway, well after wasting time tracking that down, I will continue to see if I can use it.

[Govert van Drimmelen]

Excel-DNA support two types of async functions:

 

* RTD-base async functions.

For the RTD-base async functions, I have made an async batch wrapper that might be worth looking into. The sample code is here https://github.com/Excel-DNA/Samples/blob/master/Misc/AsyncBatchSample.cs:

 

* Native Excel async functions, which you seem to be using

For the native async functions you are right that Excel-DNA has no easy support for setting multiple results at once. But it might be as easy as adding a call like this:

 

        public static bool SetResults(object[] asyncHandles, object[] results)

        {

            XlCall.XlReturn callReturn = XlCall.TryExcel(XlCall.xlAsyncReturn, out object _, asyncHandles, results);

        }

 

Let us know if you give it a try.

 

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

[Hughes Hilton]

I actually tried something very similar to that first. I exposed the _handle on ExcelAsyncHandle publicly.  They I made an array of handles and an array of results and called XlCall.TryExcel and passed in XlCall.xlAsyncReturn and the arrays.  However, this did not work. I think it's because the documentation for xlAsyncReturn says that you need to pass the value array in as the result array to the Excel12v function. ExcelDna always passes in an XlOper12 with XlTypeEmpty to the result parameter. I tried modifying ExcelDna to pass in my second array to the result parameter using the existing param marshaller, but that throws an exception so I don't think it's getting marshalled quite right for that argument. :\

Hughes

[Govert van Drimmelen]

It might work if you don’t change the data types yourself, but let the built-in marshalling build the right XLopers.

Would you be able to try the snippet I sent?

If that does not work, it would help if you could post as a GitHub Gist or elsewhere a bit of code I can run as a test to sort it out.

 

-Govert

--

[Hughes Hilton]

Yeah I actually did try the exact thing that you sent. I was just saying that in order to do that I had to expose the IntPtr Handle on the ExcelAsyncHandle so I could create an array of them to pass in.  Unless you are suggesting that I ought to just pass in an array of ExcelAsyncHandle instead? I can try that as well.  Also, here is the code I added to XlCallImp.TryExcelImp12 in order to try and make xlAsyncReturn work according to the Excel doc (which throws an exception):

                if (xlFunction == (16 | 0x4000) && parameters.Length == 2) // xlAsyncReturn

                {

                    // For xlAsyncReturn, ppOperParameters should be the array of handles and pResultOper

                    // should be the array of values.

                    XlOper12** ppOperParameters = (XlOper12**)paramMarshaler.MarshalManagedToNative(parameters[0]);

                    XlOper12** ppResultOper = (XlOper12**)paramMarshaler.MarshalManagedToNative(parameters[1]);

                    xlReturn = Excel12v(xlFunction, parameters.Length, ppOperParameters, *ppResultOper);

                }

Thank you so much for looking into this! I will also test out passing the array of ExcelAsyncHandle directly to see if the marshaller handles that correctly.

Regards,

Hughes

[Hughes Hilton]

Govert, you were right!  I commented out the code that I added to TryExcelImp12() and tried passing in the array of ExcelAsyncHandle's rather than the array of IntPtr's to XlCall.TryExcel and it worked! Thanks so much for your help! For future reference, it's just this call:

var result = XlCall.TryExcel(XlCall.xlAsyncReturn, out _, handles, results);

Where handles is a single dimension object array containing ExcelAsyncHandle and results is a single dimension object array containing the corresponding results for each handle.

Regards,

Hughes 

[Govert van Drimmelen]

Yes - please try with sending the ExcelAsyncHandles in the object[] array - the marshalling processes these differently. 

-Govert

[Hughes Hilton]

Just in case you didn't see my previous post, it worked! Yay!  See my previous post for details, but it does work so that is great.  Thanks for the help!

Regards,

Hughes

On Tuesday, April 23, 2019 at 10:12:16 AM UTC-7, Govert van Drimmelen wrote:

Yes - please try with sending the ExcelAsyncHandles in the object[] array - the marshalling processes these differently. 

-Govert

On Tue, 23 Apr 2019, 18:41 Hughes Hilton, <fhughe...@gmail.com> wrote:

Yeah I actually did try the exact thing that you sent. I was just saying that in order to do that I had to expose the IntPtr Handle on the ExcelAsyncHandle so I could create an array of them to pass in.  Unless you are suggesting that I ought to just pass in an array of ExcelAsyncHandle instead? I can try that as well.  Also, here is the code I added to XlCallImp.TryExcelImp12 in order to try and make xlAsyncReturn work according to the Excel doc (which throws an exception):

                if (xlFunction == (16 | 0x4000) && parameters.Length == 2) // xlAsyncReturn

                {

                    // For xlAsyncReturn, ppOperParameters should be the array of handles and pResultOper

                    // should be the array of values.

                    XlOper12** ppOperParameters = (XlOper12**)paramMarshaler.MarshalManagedToNative(parameters[0]);

                    XlOper12** ppResultOper = (XlOper12**)paramMarshaler.MarshalManagedToNative(parameters[1]);

                    xlReturn = Excel12v(xlFunction, parameters.Length, ppOperParameters, *ppResultOper);

                }

Thank you so much for looking into this! I will also test out passing the array of ExcelAsyncHandle directly to see if the marshaller handles that correctly.

Regards,

Hughes

--
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 exce...@googlegroups.com.

[Govert van Drimmelen]

OK great.

Would it be possible for you to post some timings of the difference between the single and array calls?

-Govert

[Hughes Hilton]

I'm attempting to figure out now how much of a difference it will make. It's not a simple test b/c our addin does a lot of things and I'm doing a proof of concept now which involves a lot of code that is not really production ready. Anyway, I'll try to report back if I find that it makes a significant difference.

Regards,

Hughes

[Hughes Hilton]

I did a few timed trials. This is pretty unscientific since I'm hitting a cloud service and not controlling the exact calculation time for the batch function execution, but my averages for 1000 functions were about 6.5 seconds when calling SetResult a single time with all result and 7.1 seconds when calling SetResult 1000 times. The times were generally tighter and more consistent with the single call. The results for multiple SetResult calls varied between 6.4 and 7.8 seconds whereas the single call results varied between 6.4 and 6.8 seconds. I can try a more scientific test later, but it seems pretty clear that the single call is faster which makes sense.

Regards,

Hughes