My own understanding of these things is not perfect.
You are right that the reason we don't cache the Application object in the ExcelDnaUtil.Application accessor is because it was preventing Excel from closing. In cases where Excel-DNA loads a COM add-in behind the scenes - like if a Ribbon is loaded
- Excel-DNA will indeed cache the Application object received in the COM add-in connection, but only for the main thread (in a ThreadStatic variable).
I now suspect that the Application object can always be cached for the main thread without danger. However, I have not been able to test this yet - you might like to try. I conjecture that only the access from another thread causes Excel to not close properly
with the outstanding references.
It is important that the Application object be properly marshalled before being used from another thread. I think the implementation of ExcelDnaUtil.Application ensures this by getting the object via the AccessibleObjectForWindow function, though the documentation
does not say anything about the apartments or threading for this function.
Now for your questions:
> Can you cache it?
If you want to access it often from other threads, you can cache the object but you need to call Marshal.ReleaseComObject() when you're done (relying on the garbage collector won't work, since it might not run for a long time). And if you never know that
you are done, your Excel process probably won't stop.
> Can you share between threads?
You need to know what COM Apartment the other threads are in. Threadpool threads are in the process MTA by default, in which case the cross-apartment marshalled interface you get from ExcelDnaUtil.Application should now live in the MTA, and can be accesses
freely from different threads. If the threads are marked as STA (as they are in your example), each thread lives in its own STA apartment, and will need its own marshalled copy.
> The errors I would expect are
RPC_E_SERVERCALL_RETRYLATER = 0x8001010A;
VBA_E_IGNORE = 0x800AC472;
You have instead of the first one the error:
RPC_E_CALL_REJECTED = 0x800100012;
which I have not seen before - is this really what you are getting?
Anyway, these errors are expected when calling the Excel object model from another thread. They indicate either that the COM message processing is busy, or that Excel has suspended the object model. Examples of ways to get these errors is to be in the process
of editing a cell, or to click and hold the mouse down on the sheet. There is no other way to handle the errors - any COM call from another thread on any Excel object might fail with one of these errors, at any time.
Your code needs to handle these calls in some way - you can decide to fail or to retry later, it doesn't matter to Excel. Considering the cell-editing case, it's clear that Excel might be in a state where your COM calls won't be processed for an indefinite
time. You need to deal with that case too - if you have some data structure that grows without bound, this might cause trouble.
You don't really explain what you are trying to do. From a performance point of view you are probably doing the worst thing possible - talking to the single threaded Excel app from many other threads at once.
It seems a bit tricky and very inefficient to use the COM marshalling to synchronize your work from other threads to the (essentially) single-threaded Excel application. It might make for a nice programming model, but you should expect rather ppor performance.
Even when you get this to be reliable it might be pretty slow. It's like trying to get lots of people out of a room by letting many people storm the door at once, instead of going through one-by-one. Much better would by to have your own timer on
the Excel main thread, and check periodically for work to do on the main thread which has been posted somewhere by the worker threads.
What is the simpler approach?
You seem to be mixing two approaches, both of which should work independently.
Make a Window (or Timer) on the Excel main thread. Then call Invoke/BeginInvoke to run code on the main thread. This should work if you don't create the extra thread in your CreateAsync function. The Window needs to run on the Excel main thread,
where it is created.
In this case you should not need to funny Excel COM errors, but the Invoke might not return or raise an error if Excel is 'busy'. I haven't tried this way.
Just have the code in your MacroRunThread, without the Dispatcher.Invoke part. Then you don't need that Window class at all. It should already be safe to call from any thread, since the ExcelDnaUtil.Application should do the marshaling
and you are ready to deal with the errors.
You seem to only ever invoke one method in the Excel object model from another thread -
Application.Run("MyPeriodicMacro") - with a macro that will process your result on the main Excel thread. This is good - you don't have multiple threads trying to talk to the object model at once, except through this method. Otherwise accessing the
object model from multiple threads is a recipe for trouble.
The worker threads can post their 'work-to-do' information onto a queue that is periodically checked and when there is some work-to-do, you invoke the "MyPeriodicMacro" macro via Application.Run. In the "MyPeriodicMacro" macro you check the queue, process
all the results without cross-thread issues or needing to handle the problematic exceptions for every COM method call.
I'll probably try to work out how to use the IGlobalInterfaceTable to marshal the interface across, but the end result will still be quite similar.
I hope that gives you some thoughts, and look forward to further questions or reports of what you are doing and learning in this regard.