Managed Excel addins

One of my current clients has got me drifting back towards my Investment Banking roots by developing an Excel addin for them. Luckily for me it’s a banking client with a twist compared to my previous banking clients; they’re happy for me to work as an external consultant, doing most of the work from my office at home and working on my contract terms.

The development work so far has been quite interesting. I’m building a prototype XLL style Excel addin that allows worksheet functions to be developed in C#. There are commercial products out there that do this, but my client wants me to write one for them. This takes me back to some work that I did for a previous banking client where I wrote some XLLs that allowed them to talk to their back-end servers from Excel using CORBA; in that system the XLLs were all in C++, this time around the idea is that the ‘meat’ of the XLL functionality will be in managed code.

The past few days have been interesting. My initial prototype was a C++ dll that hosted the CLR. This was, in retrospect, always going to have problems. The CLR can only be hosted once within a process and, once loaded, it can’t be unloaded. XLLs by their very nature can be unloaded and reloaded and “other things” could cause Excel to host the CLR itself… The initial prototype did, at least, allow me to get a thin thread of functionality all the way through from Excel to managed code and back, but it quickly became apparent that it was a dead-end.

The current prototype involves a straight C++ XLL which dynamically loads a C++/CLI dll which then configures a new AppDomain and loads the managed addin code. Since one of my client’s requirements is “xcopy deployment” (and the ability to configure the managed XLL loader to load numerous managed addins) the first problem to overcome was that of loading code into a new AppDomain when the new AppDomain had a different assembly search path to the default domain. Due to the way communication between AppDomains is managed this was a little more complex than I expected.

To load an assembly into a new AppDomain and then execute code in that AppDomain you need to also load the assembly that contains the types that you’re accessing in the new AppDomain into the AppDomain that’s doing the loading… Well, you do if you want to use a class that’s derived from MarshalByRefObject and which uses transparent proxies to talk across AppDomain boundaries.

The recommended way to do things, with code like this:

      AppDomainSetup ^setup = gcnew AppDomainSetup();
      
      setup->ApplicationName = "My App";
      setup->ApplicationBase = gcnew String(GetModulePathName(hInst));
  
      AppDomain^ newDomain = AppDomain::CreateDomain( "newDomain", nullptr, setup );
  
      Object ^obj = newDomain->CreateInstanceFromAndUnwrap(
         "MyAssembly",
         "MyType");
  
      MyType ^myType = (MyType^)obj;
  
      myType->DoThing();

requires that “MyAssembly” is loaded into both the newly created AppDomain (as that’s where you want to run the code!) and also into the current AppDomain (as “MyType” is needed by the transparant proxy to allow you to talk across AppDomain boundaries to the code in the new AppDomain.

Since the default AppDomain has an assembly search path that’s different to the assembly search path for the new AppDomain we have to somehow make the required assembly available to both AppDomains.

My first step was to include the required type in the assembly that’s doing the AppDomain creation. I figured that since this assembly was already loaded in the AppDomain that things would “just work”. Unfortunately they didn’t. The code above resulted in an exception when the cast from obj to MyType took place; “Unable to cast transparent proxy”. Changing the cast to a dynamic_cast solved that problem but didn’t actually work, the resulting object was in the wrong AppDomain.

It seems that even though the assembly that contained the type that was required was already loaded in the AppDomain it was still loaded again during the cast (possibly because of some identity assembly issue due to the fact that the executing assembly was loaded as a C++ dll which just happens to contain managed code…). Anyway. The assembly loader was looking to load the assembly again and when it did it treated the two copies of the assembly that it had loaded as different and the types were not considered to be equal… Which makes sense, in a frustrating kind of way.

After banging my head on the desk for a while I came up with a solution. I needed to get involved with the assembly loading for the current AppDomain and registering a ResolveEventHandler for the AppDomain’s AssemblyResolve event could allow that… I plugged in the event handler and tried to run the code again, the handler was called and the full name of my assembly was passed in… By implementing my event handler like this:

ref class Resolver
{
   public :
  
      static Assembly ^Resolve(
         Object ^sender, 
         ResolveEventArgs ^args)
      {
         if (args->Name == Assembly::GetExecutingAssembly()->FullName)
         {
            return Assembly::GetExecutingAssembly();
         }
  
         return nullptr;
      }
};

I could return the current assembly when asked for it, and prevent another copy being loaded… This seemed to be enough to get the code to work…

      AppDomain::CurrentDomain->AssemblyResolve += gcnew ResolveEventHandler(Resolver::Resolve);
  
      AppDomainSetup ^setup = gcnew AppDomainSetup();
      
      setup->ApplicationName = "My App";
      setup->ApplicationBase = gcnew String(GetModulePathName(hInst));
  
      AppDomain^ newDomain = AppDomain::CreateDomain( "newDomain", nullptr, setup );
  
      Object ^obj = newDomain->CreateInstanceFromAndUnwrap(
         Assembly::GetExecutingAssembly()->Location,
         "MyType");
  
      MyType ^myType = (MyType^)obj;
  
      myType->DoThing();

Will result in DoThing() being executed in the new AppDomain.

And now, finally, I can write the code that loads the managed addin assembly…