XlCall inside an ExcelAsyncFunction

[Graeme Smith]

We have converted most of our functions to use the ExcelAsyncFunction attribute.

In some cases, however, we need to get the reference of the cell calling the function.

Previously we would use 

ExcelReference caller = XlCall.Excel(XlCall.xlfCaller) as ExcelReference;

Now, however, when we do that from inside an ExcelAsyncFunction, we get an ExcelDna.Integration.XlCallException.

Is there a workaround to get the cell reference from within an ExcelAsyncFunction?


Thanks
Graeme

[Graeme Smith]

Just to add, this works fine if the method is an async Task<T> method (and the XlCall is done before an await), but fails if the method is not async returning a Task.

ie, this works:

[ExcelAsyncFunction]
public static async Task<string> Works()
{

ExcelReference caller = XlCall.Excel(XlCall.xlfCaller) as ExcelReference;

return $"R{caller.RowFirst}C{caller.ColumnFirst}:{caller.SheetId}";
}

This doesn't:

[ExcelAsyncFunction]
public static string DoesNotWork()
{

ExcelReference caller = XlCall.Excel(XlCall.xlfCaller) as ExcelReference;

return $"R{caller.RowFirst}C{caller.ColumnFirst}:{caller.SheetId}";
}

[Govert van Drimmelen]

Hi Graeme,

The Excel C API (which you access via XlCall.Excel...) can (mostly) only be used on the main Excel thread.

So, when you're in the 'async' part of a function it is expected to fail.

For the 'Works()' case, the code up to the first 'await' runs on the thread that enters the function, which is still the Excel calculation thread.

The Excel-DNA Registration rewrites your non-async function to an async one by just wrapping it into a call to Task.Run.

That means all the code in your function will then run on the Task's threadpool thread.

One workaround would be to write the wrapper yourself, in terms of a Task<T> returning function.

Otherwise, you can also implement the async function in terms of the AsyncTaskUtil helper yourself.

Something like 

[ExcelFunction]

public static object DownloadStringFromURL(string url)
{
var functionName = nameof(DownloadStringFromURL);
var parameters = new object[] { url };
HttpClient myHttpClient = new HttpClient();

// Here you can still get hold of the caller ...

return AsyncTaskUtil.RunTask(functionName, parameters, async () =>
{
//The actual asyncronous block of code to execute.
return await myHttpClient.GetStringAsync(url);
})
}

You do have to be pretty careful in how you use the ExcelReference that is returned from xlfCaller later.

First, you have to be back on the main thread to use it.

Then, it's not easy to determine whether the reference (particularly the sheet pointer in the reference) is still valid.

If it's no longer valid (say the sheet does not exist anymore) then you can get Excel to crash by using the ExcelReference.

One workaround is to convert to ExcelReference to a string immediately after you get it giving you something like [Sheet1]!A1.

That's safe to store and then use later, but now the sheet might have been renamed, and your string reference is no longer valid.

So, by trying to capture the caller ExcelReference for later us in an async function, you are playing with fire.

Can you say more about your use case?

-Govert

[Graeme Smith]

Thanks Govert

To give some further context: We're retrieving items from an API, and storing them in a cache, and returning a lookup key to the frontend for reference in downstream calculations.

Part of the key returned is the cell address (there's probably other options, but this is the current design).

Most of our async methods return Task<T>, and thus, as mentioned, as long as you get the cell address before performing any awaits, we're all good.

Some of our methods, however, are ostensibly async (in that they have the ExcelAsyncFunction attribute) but return T, because they do .GetAwaiter().GetResult() inside, which, if called inside an async method returning Task<T>, can hang Excel (as per our previous discussion here: https://groups.google.com/g/exceldna/c/eYD5ktsO4TM)

So, as far as I can tell, our options are to try to extend the async registration process performed by ExcelDna.Registration to somehow allow for storing the cell reference, or to change our key generation logic.

Unless I've missed something?

Thanks

Graeme

[Graeme Smith]

Ok, have found a workaround

We can explicitly call AsyncTaskUtil.RunAsTask inside the method, after storing the cell address.

eg:

[ExcelFunction]
public static object Works2()

{
ExcelReference caller = XlCall.Excel(XlCall.xlfCaller) as ExcelReference;

return AsyncTaskUtil.RunAsTask(
nameof(Works2),
new string[0],
() =>
{
Task.Delay(5000).GetAwaiter().GetResult();

return $"R{caller.RowFirst}C{caller.ColumnFirst}:{caller.SheetId}";
}

);
}

This achieves the desired result.