Excel Interop Types in Shimmed Automation Add-ins
In my previous posts on shimming Excel automation add-in UDFs, I've used simple examples where the methods tended to take and return standard types such as doubles and ints. Someone asked a question recently - what about Excel types as parameters? In other words, how do you write the shim for a method that takes say an Excel Range object as a parameter?
So, here's an example.
The simplest process to adopt is to build a regular COM add-in (that implements IDTExtensibility2), and then shim it. This way, you can use the COM Shim Wizards to generate the shim code. You can always remove the IDTExtensibility2 implementation from the add-in and shim later, if you don’t want them. That said, it is generally useful to implement IDTExtensibility2 in your automation add-in, if only because Excel then gives you an Excel.Application object up front – which is otherwise somewhat difficult to get.
In my managed automation add-in assembly, I declare my UDF in a custom interface. Note that I’m using standard Excel interop types, such as Excel.Range:
namespace RangeUDF
{
[GuidAttribute("73570AB7-89E3-45e4-B605-B6DDFFE7C437")]
public interface IRangeCalc
{
double SumValues(Excel.Range r);
}
…and implement it in a class like this:
[GuidAttribute("3C8ED961-B58B-4580-AB67-A45648E2A7B7"),
ProgId("RangeUDF.Connect"),
ClassInterface(ClassInterfaceType.AutoDual)]
public class Connect :
Object, Extensibility.IDTExtensibility2, IRangeCalc
{
#region Standard IDTExtensibility2 stuff
public Connect(){}
public void OnConnection(object application,
Extensibility.ext_ConnectMode connectMode,
object addInInst, ref System.Array custom)
{
xl = (Excel.Application)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 Excel.Application xl;
private object addInInstance;
#endregion
#region IRangeCalc Members
public double SumValues(Excel.Range r)
{
double sum = 0;
for (int row = 1; row <= r.Rows.Count; row++)
{
for (int col = 1; col <= r.Columns.Count; col++)
{
sum += Convert.ToDouble(
((Excel.Range)r.Cells[row, col]).Value2);
}
}
return sum;
}
#endregion
}
}
Note: make sure you know what the GUID is for your assembly. The simplest way to do this is to go to the properties for the project, then the Application tab, and click on Assembly Information. Specify a new GUID in the GUID field. This ends up in the assemblyinfo.cs. At the same time, check the box to make the assembly COM-visible, and on the build tab, check the box to register it for COM interop. For example, in my assemblyinfo.cs, I end up with this declaration:
[assembly: GuidAttribute("917E2CFA-D16C-41b4-BD33-6EE0254C98FD")]
...this is the GUID for the assembly (and therefore, eventually, the COM typelib).
So far, nothing unusual.
Now for the shim. You can use the COM Shim Wizards to generate the basic COM add-in shim code as usual. Then, you need to modify the code to turn it into an automation add-in shim.
The first critical change is to import the typelib for the managed assembly into the shim project. If you look at the stdafx.h, you’ll see #imports for the mscorlib an msaddndr typelibs, which give you the declarations of _AppDomain and IDTExtensibility2, respectively. For your shim, add another #import for the typelib of your managed assembly, eg:
// Imports the typelib for the RangeUDF add-in assembly.
#import "libid:917E2CFA-D16C-41b4-BD33-6EE0254C98FD" raw_interfaces_only named_guids
Recall that the stdafx.h is used for the precompiled headers. This #import will import the typelib and therefore generate a .TLH. You should get intellisense for this typelib in your project straight away. If you want to see a persisted copy of the .TLH, you can build your project at this point.
In the ConnectProxy.h, update the proxy class so that it implements your custom interface in addition to (or instead of) IDTExtensibility2. As soon as you type the namespace for the custom interface (and the double colon), intellisense/autocomplete should give you a dropdown list of members from the typelib:
class ATL_NO_VTABLE CConnectProxy :
public CComObjectRootEx<CComSingleThreadModel>,
public CComCoClass<CConnectProxy, &CLSID_ConnectProxy>,
public IDispatchImpl<AddInDesignerObjects::_IDTExtensibility2,
&AddInDesignerObjects::IID__IDTExtensibility2,
&AddInDesignerObjects::LIBID_AddInDesignerObjects, 1, 0>,
public IDispatchImpl<RangeUDF::IRangeCalc,
&RangeUDF::IID_IRangeCalc,
&RangeUDF::LIBID_RangeUDF, 1, 0>
{
As well as (or instead of) the IDTExtensibility2 pointer that the wizard gives you, add a declaration for your custom interface pointer:
protected:
RangeUDF::IRangeCalc *m_pRangeCalc;
Initialize your custom interface pointer in the constructor:
CConnectProxy() : m_pConnect(NULL), m_pCLRLoader(NULL), isShutdownInProgress(false), m_pRangeCalc(NULL)
{
}
Note that the isShutdownInProgress field is specific to the VS 2005 version of the COM Shim Wizards.
Update the COM map to (a) include your custom interface, and (b) route queries for IDispatch through your custom interface:
BEGIN_COM_MAP(CConnectProxy)
//COM_INTERFACE_ENTRY2(IDispatch, AddInDesignerObjects::IDTExtensibility2)
COM_INTERFACE_ENTRY(RangeUDF::IRangeCalc)
COM_INTERFACE_ENTRY2(IDispatch, RangeUDF::IRangeCalc)
COM_INTERFACE_ENTRY(AddInDesignerObjects::IDTExtensibility2)
END_COM_MAP()
If you’re using the VS 2005 version of the COM Shim Wizards, update the OnDisconnection proxy to clean up your custom interface pointer:
STDMETHOD(OnDisconnection)(
AddInDesignerObjects::ext_DisconnectMode RemoveMode, SAFEARRAY **custom )
{
HRESULT hr = S_OK;
if (m_pConnect)
{
hr = m_pConnect->OnDisconnection(RemoveMode, custom);
if (SUCCEEDED(hr))
{
m_pConnect->Release();
m_pConnect = NULL;
m_pRangeCalc->Release();
m_pRangeCalc = NULL;
// Only try to unload the AppDomain if the host application
// has not started a shutdown.
if (!isShutdownInProgress)
{
if (m_pCLRLoader)
{
// Unload the AppDomain, and null the pointer to make
// sure we only do this once.
hr = m_pCLRLoader->Unload();
m_pCLRLoader = NULL;
}
}
}
}
return hr;
}
If you’re implementing IDTExtensibility2 in your shim, you can leave the rest of the IDTExtensibility2 methods just as the wizard generated them.
Next, implement your custom interface method(s), as illustrated below. Once you have the .TLH for your custom typelib, all the types declared in it become available to intellisense. So, for example, declaring an Excel Range parameter turns out to be pretty simple:
//IRangeCalc implementation:
STDMETHOD(SumValues)(RangeUDF::Range *r, double *pRetVal)
{
return m_pRangeCalc->SumValues(r, pRetVal);
}
Finally, in the ConnectProxy.cpp, find the implementation of FinalConstruct. The wizard-generated code will give you everything you need to set up the shim and connect it to the managed assembly. You need to add a line to get from the IDTExtensibility2 pointer to the custom UDF interface, via QueryInterface, eg:
HRESULT CConnectProxy::FinalConstruct()
{
HRESULT hr = S_OK;
// Create an instance of a managed addin
// and fetch the interface pointer
m_pCLRLoader = CCLRLoader::TheInstance();
IfNullGo(m_pCLRLoader);
IfFailGo(m_pCLRLoader->CreateInstance(AssemblyName(), ConnectClassName(),
__uuidof(IDTExtensibility2), (void **)&m_pConnect));
// Initialize the RangeCalc pointer.
IfFailGo(m_pConnect->QueryInterface(
__uuidof(RangeUDF::IRangeCalc), (void **)&m_pRangeCalc));
Error:
return hr;
}
The last step is to build everything, make sure the managed assembly lives in the same folder as the shim DLL, make sure the shim is registered, and make sure the add-in typelib is registered. You should configure your setup project to do all these things, and/or custom build steps for your shim project. Alternatively, for the registration pieces, you can write a simple .REG file and merge it into the registry:
Windows Registry Editor Version 5.00
[HKEY_CLASSES_ROOT\RangeUDF.Connect]
@="RangeUDF.Connect"
[HKEY_CLASSES_ROOT\RangeUDF.Connect\CLSID]
@="{3c8ed961-b58b-4580-ab67-a45648e2a7b7}"
[HKEY_CLASSES_ROOT\CLSID\{3c8ed961-b58b-4580-ab67-a45648e2a7b7}]
@="RangeUDF.Connect"
[HKEY_CLASSES_ROOT\CLSID\{3c8ed961-b58b-4580-ab67-a45648e2a7b7}\InprocServer32]
@="C:\\Temp\\RangeUDF_shimmed\\RangeUDFShim\\Debug\\RangeUDFShim.dll"
"ThreadingModel"="Apartment"
[HKEY_CLASSES_ROOT\CLSID\{3c8ed961-b58b-4580-ab67-a45648e2a7b7}\ProgID]
@="RangeUDF.Connect"
[HKEY_CLASSES_ROOT\CLSID\{3c8ed961-b58b-4580-ab67-a45648e2a7b7}\Programmable]
[HKEY_CLASSES_ROOT\CLSID\{3c8ed961-b58b-4580-ab67-a45648e2a7b7}\TypeLib]
@="{917E2CFA-D16C-41b4-BD33-6EE0254C98FD}"
[HKEY_CLASSES_ROOT\TypeLib\{917E2CFA-D16C-41b4-BD33-6EE0254C98FD}]
[HKEY_CLASSES_ROOT\TypeLib\{917E2CFA-D16C-41b4-BD33-6EE0254C98FD}\1.0]
@="RangeUDF"
[HKEY_CLASSES_ROOT\TypeLib\{917E2CFA-D16C-41b4-BD33-6EE0254C98FD}\1.0\0]
[HKEY_CLASSES_ROOT\TypeLib\{917E2CFA-D16C-41b4-BD33-6EE0254C98FD}\1.0\0\win32]
@="C:\\Temp\\RangeUDF_shimmed\\RangeUDFShim\\Debug\\RangeUDF.tlb"
[HKEY_CLASSES_ROOT\TypeLib\{917E2CFA-D16C-41b4-BD33-6EE0254C98FD}\1.0\FLAGS]
@="0"
[HKEY_CLASSES_ROOT\TypeLib\{917E2CFA-D16C-41b4-BD33-6EE0254C98FD}\1.0\HELPDIR]
@="C:\\Temp\\RangeUDF_shimmed\\RangeUDFShim\\Debug\\"
As you can see, I’m putting the managed assembly, its typelib and the shim all in the same folder and registering them there. Build everything, run Excel and register the automation add-in shim via Tools | Add-ins | Automation, and you’re done.
Comments
- Anonymous
November 29, 2006
Hi,!! Awesome !!I think one problem with Automation Shims are that we need to update them whenever we change our managed assemblies ( add new UDFs).thanks - Anonymous
March 06, 2007
I created a test project using this code. I would expect the add-in to run in it's own app domain, but it doesn't appear to be.Adding a breakpoint at Connect.OnConnection and checking the value of AppDomain.CurrentDomain.GetData("APP_CONFIG_FILE") shows:"C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE.config"What could be the problem?Thanks - Anonymous
August 24, 2007
Hi Andrew,Thanks for all the great blog articles!I am developing a UDF in a C# COM Add-In (with shim) and want to access CustomDocumentProperties in the parent Excel Workbook containing the cell with the UDF formula. The CustomDocumentProperties contain parameters to a web service.Application.ActiveWorkbook gives me the currently active Workbook, not necessarily the one containing the UDF. Also Application.ThisWorkbook appears to refer to the Add-In itself.I can see that your technique above would allow me to determine the parent Workbook of the Range supplied to the UDF, but is not reliable as the user could select a range in a different Workbook from the one calling the UDF.Is there an easy way of determining the parent Workbook that initiates the call to a UDF, preferably in Excel 2003?Regards,Duncan. - Anonymous
August 25, 2007
I solved my problem using:Excel.Range range = (Excel.Range) application.get_Caller(System.Type.Missing); Regards,Duncan. - Anonymous
January 23, 2008
I've been thinking more about calling unmanaged XLL UDFs from managed code than about managed automation - Anonymous
January 23, 2008
I've been thinking more about calling unmanaged XLL UDFs from managed code than about managed automation