Jaa

Delay-loading the CLR in Office Add-ins

  • Artikkeli

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:

LB

Meaning

Status in COM Add-ins Dialog

Behavior Description

0

Unloaded

Disconnected, Unloaded

The add-in is not loaded when the application starts. It can be loaded through the COM Add-ins dialog box or programmatically, but reverts to “Unloaded” when the application closes.

1

Loaded

Disconnected, Loaded

The add-in is not loaded when the application starts (despite the status of “Loaded”). It can be loaded through the COM Add-ins dialog box or programmatically, but reverts to “Loaded” when the application closes.

2

Boot-Loaded

Disconnected, Load at Startup

The add-in is not loaded when the application starts. It can be loaded through the COM Add-ins dialog box or programmatically. Once the add-in is loaded, it remains loaded until it is explicitly unloaded, that is LoadBehavior is set to 3, and this status is persisted in the registry across sessions.

3

Boot-Loaded

Connected, Load at Startup

The add-in is loaded when the application starts. It remains loaded until it is explicitly unloaded.

8

Demand-Loaded

Disconnected, Load on Demand

The add-in will be loaded and connected when the host application requires it, eg, when a user clicks on a button that uses functionality in the add-in, that is LoadBehavior is set to 9.

9

Demand-Loaded

Connected, Load on Demand

The add-in will be loaded and connected when the host application requires it, eg, when a user clicks on a button that uses functionality in the add-in.

16

Connect First Time

Connected, Load on Demand (currently loaded)

The add-in loads as soon as the application starts the first time after the add-in is registered. Typically, the add-in creates a button or menu item for itself. The next time the user starts the application, the add-in is loaded on demand (LB=8), that is, it doesn't load until the user clicks the button or menu item associated with the add-in. This sets the LoadBehavior to 9.

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.