Is ExcelAsyncUtil.QueueAsMacro( async () ... ) Valid?

[Terry Aney]

Is it safe to use this pattern to accomplish:

1) Ability to await async methods?

2) Ability to access COM objects?

3) Ability to use all Excel C-API features?

Guess just a yes/no is warranted instead of effort of explaining.  It seems to be working to support all three of those and every time I've checked (not sure if possible to just be lucky??), System.Threading.Thread.CurrentThread.ManagedThreadId == 1.

[Govert van Drimmelen]

No, that's not the intended use. Though it depends - the code after the first await might run in the wrong context, but the initial part is OK (bug that completely defeats the point of the async).

-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 view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/c5370cd3-ae8d-4913-a642-0198fc707cfen%40googlegroups.com.

[Terry Aney]

Thanks.  I guess my real question is...how can I call an async function and within that function access MS Excel COM objects (i.e. current worksheet, ranges, etc.)?  Or is that not possible?  In all my other questions about async, I've been able to call COM stuff within `QueueAsMacro` context and any async stuff, I launched in separate context that maybe or may not just continue this path of launch nested QueueAsMacro and additional 'async' contexts.

But this last feature I need to support unfortunately I can't use C API because of this issue.

During .NET Core Debug Session in VS Code:

ExcelDna.Integration.XlCall.Excel( ExcelDna.Integration.XlCall.xlfEvaluate, "=IF(BTRContains(\"terry\", \"er\"),1,2)" )
1
ExcelDna.Integration.XlCall.Excel( ExcelDna.Integration.XlCall.xlfEvaluate, "=IF(BTRContains(\"terry\", \"er\"),IF(BTRContains(\"terry\", \"er\"),1,2),3)" )
ExcelErrorName

However, the async code flow jumps back and forth between needing access to 'excel info' and doing 'async work' too often to try and follow the aforementioned pattern.  Worse off is that this code is in a helper assembly that doesn't know anything about the Excel UI thread context issue.  Using COM, it was as simple as making a hidden worksheet and doing this:

        wsEvaluate.Range[ "A1" ].Formula = formula;

        var value = wsEvaluate.Range[ "A1" ].Value;

Struggling to think of a possible work around I could pull off :(

[Terry Aney]

Found this stating:

Calling Excel asynchronously by using COM from a DLL or XLL is not recommended.

So..I'm trying to change my entire flow to use C API.  Hitting a few roadblocks that maybe you've encountered???

1. How to set the Workbook Saved property?  I can read it, not found a way to set it yet.

2. Sorting a range/reference.  I found the method, but the COM version has a XlSortDataOption and XlSortMethod parameter.  I'm not finding equivalent capability in C API.

[Govert van Drimmelen]

Hi Terry, 

I would not at all use that quote as a reason to avoid the COM object model.

In the Excel-DNA add-in context where the C API as safe to use, I believe the COM object model is too, and often better.

The whole QueueAsMacro / main thread / async story should be considered the same for both options.

I'm more cautious about using the COM object model inside functions, called during calculation.

-Govert

To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/be89d285-1e26-4ba6-8219-5db12bf07762n%40googlegroups.com.

[Terry Aney]

This stuff is impossible ;) lol.  I thought COM access was only allowed on main thread (QueueAsMacro) and therefore not safe to be called from within a new thread (i.e. Task.Run( async () => { ... } ) ).  I must be misunderstanding what I've read in other threads.  Will go re-read.  Thanks for the reply.

[Govert van Drimmelen]

Everything you say is correct.

COM access, like C API, should only be done from the main thread.

The idea is to use QueueAsMacro from a worker thread to set up code that will run on the main thread. So you can run C API or COM code inside the QueueAsMacro delegate.

'async' inside QueueAsMacro makes no sense. That (might) start code running in a worker thread, where you can't do COM or C API. So you're correct, COM and C API are not safe in Task.Run.

-Govert

To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/c3156a11-9039-4862-a991-dc664c612484n%40googlegroups.com.