Delay-loading the CLR in Office Add-ins

Suppose you control your enterprise desktops to the extent that you control which add-ins are installed. Suppose, further, that you want to avoid the hit of loading the CLR at application startup. One way is to delay-load your managed add-ins. The registered LoadBehavior for an Office add-in governs how the add-in is loaded (surprise). Note these values are in decimal:

Let’s say you have a number of managed add-ins, and you want to be able to defer (or even completely avoid) loading them until you’re sure that they are needed in the current session. For example, let’s say that your Excel users sometimes work with workbooks that you care about – perhaps these workbooks have some custom property that identifies them as being part of some solution. Sometimes the users work on workbooks that are not part of any enterprise solution. If, during a session, the user only works on workbooks you don’t care about, you want to avoid loading any managed add-ins. In this way, the users avoid the perf hit of loading the CLR unless the custom functionality in your managed add-ins is actually required.

You can use the standard Office delay-load mechanism as noted in the LoadBehavior table above. The constraint here is that you’re dependent on some user action to notify Office that it needs to load the add-in, and that might not fit your requirements.

Another way you could achieve this is to build a native add-in that performs the test of whether or not to load the managed add-ins (and therefore, the CLR). For example, this could examine each workbook (or document in Word, presentation in PowerPoint, etc) that the user opens, to decide whether or not to load the managed add-ins.

The required operations for this mechanism are pretty simple. Each Office application exposes its collection of registered add-ins in a COMAddIns collection. In this collection, each add-in is represented by a COMAddIn object – this is true for all registered add-ins, regardless of whether the add-in is actually loaded or unloaded (and regardless of its LoadBehavior value). The COMAddIn interface exposes a number of properties, of which two are particularly interesting in this context:

· The Object property, which represents any arbitrary object that your add-in wants to expose for external automation. See my post on RequestComAddInAutomationService for more details of this.

· The Connect property, which represents the connected state of the add-in: true=connected, false=registered but disconnected. Note that for VSTO add-ins, if the add-in is not connected it’s also not loaded (and the runtime has not created an appdomain for it).

So, to defer or avoid loading the CLR, you can build a native add-in that conditionally sets the Connect property on your managed add-in(s).

To test this out, I created a Shared Add-in in C++. In the stdafx.h, I added #imports for the Office and Excel typelibs (my add-in targets Excel):

// Import the latest Office type library based on its registration.

#import "libid:2DF8D04C-5BFA-101B-BDE5-00AA0044DE52" \

named_guids, auto_search, auto_rename, \
rename("_IID_Adjustments", "Office_IID_Adjustments")

using namespace Office;

// Latest registered Excel typelib.

#import "libid:00020813-0000-0000-C000-000000000046" \

       auto_search, auto_rename

using namespace Excel;

Then, I replaced the declaration of the generic IDispatch smart pointer that the shared add-in project gives you for the application object with an Excel-OM Application smart pointer, that is, changed this:

       CComPtr<IDispatch> m_pApplication;

…to this:

       Excel::_ApplicationPtr m_spExcel;

…and initialized it in the OnConnection method from the incoming IDispatch* (the smart pointer assignment performs a QI for me):

       m_spExcel = pApplication;

Note that I could have used the IDispatch pointer and late binding to get the COMAddIns collection, but it’s just easier to use strong typing. The only other significant task was to provide behavior to connect and disconnect my target VSTO add-in(s). In this example, the VSTO add-in I want to control has a registered ProgID of “VstoExcelAddIn”:

// Get the VstoExcelAddIn from the COMAddIns collection.

Office::COMAddInPtr spCOMAddIn;

CComVariant vtItem("VstoExcelAddIn");

spCOMAddIn = m_spExcel->COMAddIns->Item(&vtItem);

// Toggle the connected state of the add-in.

VARIANT_BOOL bOldState = spCOMAddIn->Connect;

if (bOldState == VARIANT_TRUE)

{

       spCOMAddIn->Connect = VARIANT_FALSE;

}

else

{

       spCOMAddIn->Connect = VARIANT_TRUE;

}

Of course, you still have to write the code that determines whether or not you want to connect/disconnect the add-in (and potentially, which add-in or add-ins you want to control). You might do this based on some custom document property – and that would require you to sink the WorkbookOpen/WorkbookBeforeClose events, and possibly the WindowActivate/Deactivate events. Or, it might be based on the current user account name and/or domain. Or, on the day of the week, or any other arbitrary condition.

So, if you want to control VSTO add-ins, you can simply build a native add-in to control them, as described above. If, on the other hand, you want to control non-VSTO managed add-ins, you could eithr use a native add-in in the same way – or alternatively, you could build this controlling functionality into your native shim. Note that this technique is not restricted to add-ins – you can build the same functionality into any of the native shims that the COM Shim Wizard supports – including managed smart tags, real-time data components, and automation add-ins.