Example Shimmed Automation Add-in
A number of people have asked me for a detailed example of how to shim a managed Excel automation add-in, so here goes.
This is a very simple automation add-in that exposes 2 functions: Fahr2Cel (converts fahrenheit to celsius) and Cel2Fahr (converts celsius to fahrenheit). Neither function is volatile, and the add-in makes no use of the Excel object model. It's a pure automation add-in, not a hybrid - that is, it does not implement IDTExtensibility2.
At its simplest, you can implement a managed automation add-in as a very simple class library from scratch. The only real difficulty comes when you want to provide an unmanaged shim for it. There is a COM Shim Wizard available for download on msdn, and this will auto-generate a complete shim for a managed COM add-in, Smart Tag or RealTime Data component. It will not generate a shim for an automation add-in. So, you have 2 choices:
- Create a Class Library project for your add-in, and build it exactly the way you want it, and then build a shim manually, OR:
- Create a Shared COM add-in project for your add-in (which implements IDTExtensibility2), use the COM Shim Wizard to auto-generate the shim project, and then strip out all the redundant IDTExtensibility2 code.
In the following steps, I've taken the second approach: it's messy but it's easier.
1. Create a Visual Studio Extensibility Shared Add-in project, called AutomationAddin. Most of the questions the Shared Add-in project wizard asks are only relevant to COM add-ins, so it pretty much doesn't matter what you answer to them. You are forced to select an application host, so you might as well select Excel (although you'll back out of this later). The name and description are arbitrary, and the LoadBehavior only applies to COM add-ins. This will generate a class library project for the add-in and a setup project. The add-in class, called Connect, will implement IDTExtensibility2.
2. At the top of the file that defines the Connect class, declare our new custom interface (use GUIDGen to get a fresh GUID):
[GuidAttribute("D5FE326A-8DD1-4e0c-BEA0-F95BDEE39574")]
public interface ITemperatureConversion
{
double Fahr2Cel(double val);
double Cel2Fahr(double val);
}
3. Modify the Connect class so that it does not implement IDTExtensibility2, but it does implement our custom ITemperatureConversion interface:
[GuidAttribute("D2B95FF2-7082-47a4-9322-511EAB6688B6"),
ProgId("AutomationAddin.Connect"), ClassInterface(ClassInterfaceType.None)]
public class Connect :
Object,
// Extensibility.IDTExtensibility2,
ITemperatureConversion
{
// public Connect() {}
//
// public void OnConnection(object application,
// Extensibility.ext_ConnectMode connectMode,
// object addInInst, ref System.Array custom)
// {
// applicationObject = application;
// addInInstance = addInInst;
// }
//
// public void OnDisconnection(
// Extensibility.ext_DisconnectMode disconnectMode,
// ref System.Array custom) {}
//
// public void OnAddInsUpdate(ref System.Array custom) {}
//
// public void OnStartupComplete(ref System.Array custom) {}
//
// public void OnBeginShutdown(ref System.Array custom) {}
//
//
// private object applicationObject;
// private object addInInstance;
private double constantFactor = 32.0;
public double Fahr2Cel(double val)
{
return ((5.0 / 9.0) * (val - constantFactor));
}
public double Cel2Fahr(double val)
{
return ((val * (9.0 / 5.0)) + constantFactor);
}
}
4. If you're going to strongname your assembly, now's the time to do it. This is generally a good idea, and may even be mandated by your organization's software standards.
5. That's the add-in done. Build it. By default, it will be set to Register for COM Interop on build. This is (half) OK. We actually don't want the add-in registered, but we do want its type library registered. If you want to do this properly, you should turn off the “Register for COM Interop“ setting, and instead run RegAsm /tlb on the assembly (you could do this as a post-build event task). If you like, you can use OLEView to check exactly what the typelib looks like, and to make sure you've registered the interface you think you've registered.
6. Now for the shim. Run the COM Shim Wizard, specifying that you want a shim for a COM add-in, and pointing the wizard to your add-in DLL. This will auto-generate a shim project with a set of C++ ATL-based code, of which about 0.5% is specific to your add-in. Now we have to start stripping out all the IDTExtensibility2 code that the wizard generated. Let's start with the stdafx.h. At the bottom of this file, you'll see a couple of #import statements for importing the mscorlib and msaddndr typelibs. We still need mscorlib, but we don't need msaddndr (because that was added for IDTExtensibility2). On the other hand, we do need to import the typelib for the add-in itself. (The GUID used in this statement comes from the assembly-level GuidAttribute declaration in the add-in's assemblyinfo.cs file):
//The following #import imports the MSADDNDR.dl typelib which we need for IDTExtensibility2.
//#import "libid:AC0714F2-3D04-11D1-AE7D-00A0C90F26F4" raw_interfaces_only named_guids
//The following #import imports the typelib for the managed assembly.
#import "libid:8ABE10B3-E686-4c81-8F15-866EF405B4C9" raw_interfaces_only named_guids
7. Next, the ConnectProxy.h file: this is the file that declares the unmanaged class that proxies the add-in. This is where we need to make most of our changes:
- Remove IDTExtensibility2 from the CConnectProxy class inheritance list.
- Remove IDTExtensibility2 from the COM map.
- Remove all the IDTExtensibility2 methods.
- Add ITemperatureConversion to the CConnectProxy class inheritance list.
- Add ITemperatureConversion to the COM map.
- Replace the double-cast for IDispatch in the COM map: instead of casting to IDTExtensibility2 to get to IDispatch, we'll cast to ITemperatureConversion.
- Add the ITemperatureConversion methods.
- Change the declaration of the cached add-in pointer from type IDTExtensibility* to type ITemperatureConversion*.
class ATL_NO_VTABLE CConnectProxy :
public CComObjectRootEx,
public CComCoClass,
//public IDispatchImpl
//&AddInDesignerObjects::IID__IDTExtensibility2,
//&AddInDesignerObjects::LIBID_AddInDesignerObjects, /* wMajor = */ 1, /* wMinor = */ 0>,
public IDispatchImpl
&AutomationAddin::IID_ITemperatureConversion,
&AutomationAddin::LIBID_AutomationAddin, /* wMajor = */ 1, /* wMinor = */ 0>
{
public:
CConnectProxy() : m_pConnect(NULL)
{
}
DECLARE_REGISTRY_RESOURCEID(IDR_CONNECTPROXY)
BEGIN_COM_MAP(CConnectProxy)
//COM_INTERFACE_ENTRY2(IDispatch, AddInDesignerObjects::IDTExtensibility2)
COM_INTERFACE_ENTRY2(IDispatch, AutomationAddin::ITemperatureConversion)
//COM_INTERFACE_ENTRY(AddInDesignerObjects::IDTExtensibility2)
COM_INTERFACE_ENTRY(AutomationAddin::ITemperatureConversion)
END_COM_MAP()
DECLARE_PROTECT_FINAL_CONSTRUCT()
HRESULT FinalConstruct();
void FinalRelease();
public:
//IDTExtensibility2 implementation:
//STDMETHOD(OnConnection)(IDispatch * Application,
// AddInDesignerObjects::ext_ConnectMode ConnectMode,
// IDispatch *AddInInst, SAFEARRAY **custom)
//{
// return m_pConnect->OnConnection(
// Application, ConnectMode, AddInInst, custom);
//}
//STDMETHOD(OnDisconnection)(
// AddInDesignerObjects::ext_DisconnectMode RemoveMode, SAFEARRAY **custom )
//{
// return m_pConnect->OnDisconnection(RemoveMode, custom);
//}
//STDMETHOD(OnAddInsUpdate)(SAFEARRAY **custom )
//{
// return m_pConnect->OnAddInsUpdate(custom);
//}
//STDMETHOD(OnStartupComplete)(SAFEARRAY **custom )
//{
// return m_pConnect->OnStartupComplete(custom);
//}
//STDMETHOD(OnBeginShutdown)(SAFEARRAY **custom )
//{
// return m_pConnect->OnBeginShutdown(custom);
//}
//ITemperatureConversion implementation:
STDMETHOD(Fahr2Cel)(double val, double* pRetVal)
{
return m_pConnect->Fahr2Cel(val, pRetVal);
}
STDMETHOD(Cel2Fahr)(double val, double* pRetVal)
{
return m_pConnect->Cel2Fahr(val, pRetVal);
}
protected:
//AddInDesignerObjects::IDTExtensibility2 *m_pConnect;
AutomationAddin::ITemperatureConversion *m_pConnect;
};
8. Next, the ConnectProxy.cpp file: this contains the remaining implementation details of the proxy class. The changes are:
- At the top of the file, remove the using statement for the AddInDesignerObjects namespace.
- Inside the class, change the CreateInstance call to specify the ITemperatureConversion interface instead of IDTExtensibility2.
IfFailGo(pCLRLoader->CreateInstance(
AssemblyName(), ConnectClassName(),
// __uuidof(IDTExtensibility2),
__uuidof(AutomationAddin::ITemperatureConversion),
(void **)&m_pConnect));
9. Finally, change the ConnectProxy.rgs file. This is the registry resource that gets embedded in the shim DLL. We don't need the HKCU (or HKLM) entries at all. In the HKCR tree, we need to add the one extra key: Programmable (no value).
HKCR
{
AutomationAddin.Connect = s 'AutomationAddin.Connect'
{
CLSID = s '{D2B95FF2-7082-47a4-9322-511EAB6688B6}'
}
NoRemove CLSID
{
ForceRemove '{D2B95FF2-7082-47a4-9322-511EAB6688B6}' = s 'AutomationAddin.Connect'
{
ProgID = s 'AutomationAddin.Connect'
InprocServer32 = s '%MODULE%'
{
val ThreadingModel = s 'Apartment'
}
Programmable
}
}
}
10. At runtime, the shim expects to find the add-in assembly in the same folder as the shim. On your development machine, you can set this up as a post-build event task in the shim project. There'll already be a post-build task to register the shim, and you can just append to this. For example:
regsvr32 /s /c "$(TargetPath)"
copy "$(SolutionDir)bin\debug\AutomationAddin.dll" "$(TargetDir)"
11. These steps are enough to get the shim working on your dev machine. So, at this point, you could build and test. When you add the add-in in Excel's UI, you should see in the dialog that it specifies the shim as the DLL to load. You can double-check this by running Process Explorer: this should show both the add-in and the shim in memory when you enter a cell formula that uses your add-in's functions.
12. To complete the solution, and so that you can deploy it to your endusers' machines, you should update your setup project. The changes are:
- In the registry editor, you can remove the entries for your add-in under HKCU (or HKLM).
- You need to add a new entry for HKCR\CLSID\{whatever your Connect class CLSID is}\Programmable
- In the filesystem editor, you need the primary output from your add-in project, the primary output from your shim project, and the add-in typelib.
- The Register property for the shim output should be vsdrpCOM, the Register property for the add-in output should be vsdrpDoNotRegister.
13. Final note: be wary of the build dependencies. By default, the setup project (modifed as indicated above) will be dependent on both the shim and the add-in. You can set the shim project to be dependent on the add-in project. However, because the shim doesn't directly reference the add-in (it uses it indirectly through COM interop at runtime), when you make changes to the add-in, it doesn't necessarily force a rebuild of the shim. Also, the add-in is getting registered when it builds, and this will overwrite the registration for the shim. Both the add-in and the shim necessarily register to the same CLSID and ProgID: we need this because we want Excel to load the shim in place of the add-in. So, always ensure that you build the shim last (or forcibly re-register it with RegSvr32).