Getting the Application Object in a Shimmed Automation Add-in
Building a managed automation add-in is one way to implement Excel user-defined functions (UDFs). The traditional way is to build an XLL and there was a healthy discussion about the pros and cons of this in one of my posts. In an even earlier one, I talked about shimming your managed automation add-ins. The reasons for shimming a managed COM add-in, along with the basic techniques, are now reasonably well-understood. However, there is an extra wrinkle for a certain class of managed automation add-ins.
Recall that a COM add-in implements IDTExtensibility2, and in the OnConnection call the Office host will pass in a pointer to its Application object. This allows the add-in to interact with the host object model (OM).
Automation add-ins may implement IDTExtensibility2, but the only point of doing this would be if you want your automation add-in to serve a dual purpose as a regular COM add-in. If Excel loads it as a COM add-in, it will use the IDTExtensibility2 interface. If Excel loads it as an automation add-in, it won't. It is very rare (and questionable design) for anyone to build a dual-purpose COM/automation add-in, so it usually doesn't make any sense for an automation add-in to implement IDTExtensibility2.
Most automation add-ins are merely function libraries, providing custom functions that are additional to the inbuilt Excel cell functions. These functions can do anything you like, and typically perform custom calculations, make use of existing class libraries of domain (business) functionality, or call back-end services. These functions are mostly independent of Excel, that is, they are unaware that they are being used in the context of Excel. Therefore, shimming automation add-ins that consist only of such functions is straightforward.
Now for the wrinkle: suppose you want to build an automation add-in that wants to use the Excel OM. How do you get hold of the OM? For a COM add-in, it's easy - you get the OM Application object for free. For an automation add-in, Excel simply calls into your functions - it doesn't pass you an Application object anywhere.
One solution is to use the native Win32 APIs that support Active Accessibility. This technology (shipped as standard with Windows) allows developers to make applications more accessible to people with vision, hearing or motion disabilities. You can take advantage of this to connect to an accessible application via the Accessibility APIs. Once you've connected to the accessible object, you can then get to its native OM (if the application supports this). To do this, you can use P/Invoke with some native APIs.
Here’s how. Let’s say we have a managed automation add-in that exposes some functions for temperature conversion. This is based on my post about volatile UDFs. The functions use a factor in their calculations which defaults to 32.0 but which can also be retrieved dynamically from the active worksheet:
public double F2C(double val)
{
double constantFactor = 32.0;
if (xl != null)
{
object missing = Type.Missing;
xl.Volatile(missing);
Excel.Worksheet sheet =
(Excel.Worksheet)xl.ActiveSheet;
if (sheet != null)
{
constantFactor = (double)((Excel.Range)
sheet.Cells[1, 1]).Value2;
}
}
return ((5.0 / 9.0) * (val - constantFactor));
}
…where xl is an Excel Application object, declared as a class member:
private Excel.Application xl;
To get hold of the Excel Application object, this is what we’ll do. From the add-in assembly, we can get hold of the current process (Excel), and from that we can get Excel’s main window. Then we can walk the list of child windows to find one that supports Accessibility. Once we’ve found that, we can use the AccessibleObjectFromWindow API to get the Excel Application object. Here’s the import for AccessibleObjectFromWindow:
[DllImport("Oleacc.dll")]
public static extern int AccessibleObjectFromWindow(
int hwnd, uint dwObjectID, byte[] riid,
ref Microsoft.Office.Interop.Excel.Window ptr);
In order to walk Excel’s list of child windows to find one that supports accessibility, we need to call EnumChildWindows. This API expects a callback function as its 2nd parameter. So, we’ll declare a delegate type that matches the signature of the callback (takes a pair of incoming and outgoing integer parameters). The incoming int will be the HWND of a window to be examined, the outgoing int will be the HWND of a window that we determine does support accessibility:
public delegate bool EnumChildCallback(int hwnd, ref int lParam);
[DllImport("User32.dll")]
public static extern bool EnumChildWindows(
int hWndParent, EnumChildCallback lpEnumFunc,
ref int lParam);
We’ll declare an instance of the delegate as a class member, to ensure that it stays alive long enough to complete its work - which might be longer than the life of the function that sets it up:
private EnumChildCallback cb;
For each window handle we're passed, we call the Win32 API function GetClassName to match the class name of the window against the known class name of an Excel window that supports accessibility ("EXCEL7"):
[DllImport("User32.dll")]
public static extern int GetClassName(
int hWnd, StringBuilder lpClassName, int nMaxCount);
public bool EnumChildProc(int hwndChild, ref int lParam)
{
StringBuilder buf = new StringBuilder(128);
GetClassName(hwndChild, buf, 128);
if (buf.ToString() == "EXCEL7")
{
lParam = hwndChild;
return false;
}
return true;
}
To put all the pieces together, we’ll implement the constructor for our add-in class to get Excel’s Application object:
public Connect()
{
// First, get Excel's main window handle.
int hwnd = (int)Process.GetCurrentProcess().MainWindowHandle;
// We need to enumerate the child windows to find one that
// supports accessibility. To do this, instantiate the
// delegate and wrap the callback method in it, then call
// EnumChildWindows, passing the delegate as the 2nd arg.
if (hwnd != 0)
{
int hwndChild = 0;
cb = new EnumChildCallback(EnumChildProc);
EnumChildWindows(hwnd, cb, ref hwndChild);
// If we found an accessible child window, call
// AccessibleObjectFromWindow, passing the constant
// OBJID_NATIVEOM (defined in winuser.h) and
// IID_IDispatch - we want an IDispatch pointer
// into the native object model.
if (hwndChild != 0)
{
const uint OBJID_NATIVEOM = 0xFFFFFFF0;
Guid IID_IDispatch = new Guid(
"{00020400-0000-0000-C000-000000000046}");
Excel.Window ptr = null;
int hr = AccessibleObjectFromWindow(
hwndChild, OBJID_NATIVEOM,
IID_IDispatch.ToByteArray(), ref ptr);
if (hr >= 0)
{
// If we successfully got a native OM
// IDispatch pointer, we can QI this for
// an Excel Application (using the implicit
// cast operator supplied in the PIA).
xl = ptr.Application;
}
}
}
}