Excel invokes .NET automation server from two different AppDomains?

I have an Excel plug-in (written in C#) with a static variable that is at the heart of a singleton data cache:

static DataCache _instance;

This is accessed via three different code paths:

1. Event handlers on a VSTO ribbon-bar initialize the instance, and also read it for display in helper dialogs
2. An RTD server (a class that is declared [ComVisible] and implements the IRtdServer interface) utilizes the data for RTD formulas
3. A set of automation calls (implemented in another class that is declared [ComVisible]) also operate on the data. These are called by way of VBA code that is invoked when buttons on the Excel worksheet are clicked.

EDIT (#3):

Depending on the order in which these code paths are first invoked, I find that my code runs in two separate AppDomains.

All access from the ribbon-bar event handlers occurs in an AppDomain called "MyPlugIn.vsto". If this is the FIRST access to my COM object, then all subsequent calls (including RTD calls) occur in the same AppDomain.

However if the FIRST access is via the RTD interface, then that call and all subsequent RTD calls occur in an AppDomain called "DefaultDomain". (This happens when loading a saved document with embedded RTD formulas.) Subsequent calls to initialize and manipulate the DataCache via the toolbar still occur in the "MyPlugIn.vsto" AppDomain. This means that the RTD formulas always run as if the DataCache were not initialized (since the static variable set in one AppDomain remains uninitialized in the other).

It appears that Excel or the VSTO is creating an AppDomain when VSTO initializes. Objects created via COM interop before this initialization land in the default AppDomain, while objects created afterwards land in the VSTO AppDomain.

How can I ensure that the same DataCache instance is utilized, no matter which AppD开发者_如何学运维omain my RTD server object gets created in?

Your static variable is certainly not shared between AppDomains, so what you are seeing is as expected, given the different AppDomains.

I think it works like this:

The VSTO add-in runs in its own AppDomain. If the COM class factory for your cache object (or RTD server) is created from within that AppDomain, it will be loaded into the calling AppDomain. Subsequent access to that COM class will find it already loaded into the process, and use the existing instance.

However, if the first activation is triggered by Excel itself, e.g. by an RTD call, the .NET implemented COM object will be loaded into the default AppDomain of the process. You have no control over this part of the loading process unless you make an unmanaged shim, since 'your code' is not running when the loading happens.

Some suggestions off the top of my head:

1. Make some wrapper functions for the RTD calls which are exposed from your .NET add-in. This way, you can ensure the RTD class is loaded before calling Excel's Application.RTD to do the real RTD setup.

2. Make the access from the RTD server to the actual cache through user-defined functions - this way Excel will call into the AppDomain that has the real cache, even if it is not the current AppDomain where the RTD server lives.

3. Try to get hold of the Add-In object via Application.AddIns.... there is a way to get the actual add-in COM object, and use some interface on it to get to the cache...

4. Make an unmanaged shim (search the web for "COM shim wizard") for your RTD server. Somehow figure out how to get your VSTO AppDomain loaded, and then load the RTD server into that AppDomain.

5. See if there is a way to get the VSTO add-in loaded into the default AppDomain. I don't know, but perhaps there is a flag or switch that tells the "Microsoft Office Systems loader" (or whatever that part is called now) not to create the isolated AppDomain.

6. The right answer: use Excel-Dna (disclaimer: I'm the developer). It supports Ribbons, RTD and UDFs in your managed add-in, with no registration needed, and everything gets put into your add-ins AppDomain. It's free, but it will take some time and effort to port your stuff - RTD is trivial, but if you use a lot of the VSTO helper objects for the Ribbon and sheet access (tables etc.), you'll need to think about it a bit.

I hope this gives you some ideas.

--Govert--

first, you may want to declare your instance as:

static DataCache _instance = new DataCache();

this way (not the only one for sure), you know _instance is generated thread safe. There is much coverage on the topic for thread safe singletons, but this seems one of the simplest solutions.

The second thing may be you want to try using is a structure like

Lock (_lockObject)
{
...
}

for both reads and writes. This will make your reads and writes safe from different threads.

Finally, but this is pure speculation, you can try by creating a separate object for your COM calls that resides in a STA and accesses your library.

Good luck!