Office 2010 introduced the 64-bit version, which supports only 64-bit COM add-ins. This meant that all add-ins created with VB6 for its VBA editor stopped to work, because they are 32-bit add-ins. The only possibilities to create 64-bit add-ins are using C++ 64-bit, or using .NET x64 and COM Interop. So, it is not a surprise that quite a few add-ins for the VBA editor are created now using .NET. I wrote the article HOWTO: Create an add-in for the VBA editor (32-bit or 64-bit) of Office with Visual Studio .NET back in 2012 along with a few others referenced at the end of that article. One thing that many developers of add-ins for the VBA editor may not know is that by default all .NET-based add-ins are loaded in the same AppDomain (a .NET concept for isolation of assemblies), and therefore are not properly isolated. A couple of bad things that can happen are the following:
- “System.InvalidCastException: Unable to cast object of type ‘<Namespace of add-in 1>.VBProjectClass’ to type ‘<Namespace of add-in 2>._VBProject'”. This exception happens because two add-ins use different Interop Assemblies (IAs) for the “Microsoft Visual Basic for Applications Extensibility 5.3” COM reference. For example, my MZ-Tools add-in uses its own local Interop Assemblies, while others use their own Interop Assemblies or Primary Interop Assemblies (PIAs) provided by Microsoft. I have good reasons to use my own IAs, but I have tested that even two add-ins using PIAs can suffer the problem depending on the version of the PIAs or their locations. This problem happens because both add-ins are loaded in the same AppDomain, otherwise it wouldn’t happen.
- Since Office is a COM application, .NET-based add-ins use Runtime Callable Wrappers (RCWs). It is important that an add-in releases its count on RCWs when no longer used, calling Marshal.ReleaseComObject. For example, if a .NET add-in calls VBE.SelectedVBComponent.CodeModule.CodePane, it is leaking RCWs for VBComponent and CodeModule. How bad is this depends on the host, but suffice to say that for VB6 you can freeze the code editor. The article Office application does not quit after automation from Visual Studio .NET client explains the issue when automating Office but it applies also to using the extensibility model for VBA. The problem is that RCWs are created by AppDomain, so when an add-in needs a RCW, if another add-in in the same AppDomain already created one for the same COM object, the same RCW is reused by the second add-in. Apart from an add-in failing to decrease its usage count on a RCW, it can also happen than an add-in decreases usage counts not belonging to it, for example calling Marshal.FinalReleaseComObject.
So, how are both problems solved? The solution is well-known for developers of add-ins for Office applications using .NET since 2002 or 2003, through the use of a COM Shim. A COM Shim is an ActiveX dll, written in C++, that acts as a native add-in, and loads the Common Language Runtime (CLR) of .NET, creates an AppDomain, and loads the .NET-based add-in. Calls to the native C++ add-in (OnConnection, OnDisconnection, etc.) are passed to the managed (.NET) add-in.
If you are a developer of .NET-based add-ins for the VBA editor, the following are the MSDN articles that you need to read, understand and apply for your add-in to be a good citizen, running isolated from other add-ins in its own AppDomain. When reading the articles, replace “add-in for Office” or “Office extension” by “add-in for VBA editor”:
Isolating Office Extensions with the COM Shim Wizard
This article is relevant because it explains all about isolation and COM Shims.
Isolating Microsoft Office Extensions with the COM Shim Wizard Version 2.0 (VS 2005)
This article is relevant because it doesn’t use the Managed Aggregator introduced in next version 2.3. Add-ins don’t require “aggregation”, just “containment” is required.
Isolating Microsoft Office Extensions with the COM Shim Wizard Version 2.3 (VS 2008)
This article is relevant because it uses “aggregation” instead of “containment”, and you need to realize that add-ins don’t require “aggregation” and you can remove with some adjustments the ManagedAggregator project and its output assembly.
COM Shim Wizards for VS 2010 (220.127.116.11)
This article is relevant because it’s the latest available COM Shim wizard with bug fixes, yet using “aggregation”. I used this and then removed “aggregation” to use “containment” of version 2.0.
Taking COM Shim Wizards to 64-bit
This article is relevant because it explains how to port the COM Shim to 64-bit.
There are two ways of knowing which AppDomain an add-in is loaded into:
Using Process Explorer, you can use the .NET Assemblies tab of the properties window of a process. For example, this image shows that MZ-Tools is loaded in its own MZToolsAppDomain using CLR 4.0:
For some reason that I don’t know, Process Explorer doesn’t show AppDomains for CLR 2.0:
The other way is calling from the own add-in System.AppDomain.CurrentDomain and showing its FriendlyName property. This image shows the MZ-Tools uses its own MZToolsAppDomain for CLR 2.0 (that Process Explorer didn’t show):
If not using the COM Shim, the AppDomain is the DefaultDomain: