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.

Comments

• Anonymous
  April 22, 2008
  Bekanntlich sind Informationen direkt von der Quelle die besten, da nicht misinterpretiert. Andrew Whitechapel
• Anonymous
  May 01, 2008
  Thanks for the article! I'd been wondering if this was possible.I'm able to get my toolbar to show up in Excel 2003 without the CLR loading on start-up (after changing the temporary argument to false when creating it), but it doesn't seem to be loading when I click a button.Do I need to change something else in my add-in to get the demand loading to work?
• Anonymous
  May 06, 2008
  Andrew - it's not clear what you're doing. Is it that you followed the explanation in my post and you have a native add-in with a custom toolbar that activates your managed add-in?Or, is it that you have only a managed add-in with a custom toolbar, and that you're relying on the inherent 'demand-load' LoadBehavior setting?
• Anonymous
  May 06, 2008
  I'm trying to avoid writing the native add-in. So I'm just relying on the 'demand-load' flags in the LoadBehavior setting. I was hoping that would cause my add-in to load on the first button press.I discovered that this works fine for the 2007 version of my add-in, but not for 2003.
• Anonymous
  May 06, 2008
  Forgot to say explicitly...yes, my managed add-in has a custom toolbar. So I want the first click on one of the buttons in the managed toolbar to load the add-in.
• Anonymous
  May 16, 2008
  Andrew - that should work. Are you sure you're hooking up the Click handler every time?
• Anonymous
  July 14, 2008
  In an earlier post , I talked about how you could delay (or prevent) the loading of managed code using
• Anonymous
  May 12, 2009
  Andrew,I am very interested in the resolution of the discussion just above but your last post is cut off.
• Anonymous
  May 12, 2009
  gregc - I never got a final response from Andrew, but not hooking up event handlers correctly is a common problem.The comment that was cut off was a trackback (these are always cut off, because they only show the beginning of the trackback post). The post that included the trackback is here: http://blogs.msdn.com/andreww/archive/2008/07/14/demand-loading-vsto-add-ins.aspx
• Anonymous
  May 13, 2009
  Andrew, Can you give me an idea of what is required to hook up the event handlers correctly in order to make this work or what some of the common mistakes are?  Some sample code would be great.I posted a question on this to the VSTO forum that has some more detail.http://social.msdn.microsoft.com/Forums/en-US/vsto/thread/ee959b22-5a19-4afc-8895-ace32c3d0a22
• Anonymous
  May 20, 2009
  gregc - it looks like you got an answer to your forum post. Just for completeness, the main problem was that your CommandBarButton.OnAction needed to be set to "!<ProgId_of_your_add-in>" - and it looks like you were'nt using the correct ProgId string value.