VSTO Add-ins, COMAddIns and RequestComAddInAutomationService
The COMAddIns property is a collection of COMAddIn objects exposed by Office applications that support COM add-ins. The COMAddIn interface defines a small number of methods/properties, such as the ProgId of the add-in and the Connect state. It also defines an Object property. By default, the value of the Object property is null. An add-in can set this value itself, and the purpose is to expose this add-in’s functionality to external callers. VSTO supports this mechanism through a new virtual method on the AddIn base class, RequestComAddInAutomationService. Most developers will never use this service, and its an example of one of the little things that VSTO does to support the widest range of add-in developers. Here’s how it works.
In your VSTO add-in class, you decide which methods you want to expose to external callers, and wrap these methods in a ComVisible class. Then, you override the RequestComAddInAutomationService virtual method to return an instance of this class. For example, we’ll expose a DisplayMessage method. The calls to this method will be made via automation, so I’m defining an automation (dual) interface to define the method:
[ComVisible(true)]
[Guid("B523844E-1A41-4118-A0F0-FDFA7BCD77C9")]
[InterfaceType(ComInterfaceType.InterfaceIsDual)]
public interface IAddinUtilities
{
void DisplayMessage();
}
I can also define a class that will implement the interface to perform some suitable operation in the method:
[ComVisible(true)]
[ClassInterface(ClassInterfaceType.None)]
public class AddinUtilities : IAddinUtilities
{
public void DisplayMessage()
{
MessageBox.Show("Hello World");
}
}
Then, in my add-in class, I override RequestComAddInAutomationService to return an instance of this class. As you can see, the programming model for this method is very similar to the generic RequestService method that VSTO uses for the new extensibility interfaces (although of course the underlying mechanism is completely different):
public partial class ThisAddIn
{
private AddinUtilities addinUtilities;
protected override object RequestComAddInAutomationService()
{
if (addinUtilities == null)
{
addinUtilities = new AddinUtilities();
}
return addinUtilities;
}
}
Then, I can create a suitable Office document and insert some VBA to call this exposed method. So, if my add-in is an Excel add-in, I can create an XLSM (macro-enabled) workbook and Alt-F11 to get the VBA editor up. I’ll put a CommandButton on the worksheet, and code it to call the DisplayMessage method:
Private Sub CommandButton1_Click()
Dim addin As Office.COMAddIn
Dim automationObject As Object
Set addin = Application.COMAddIns("ExcelAddinService")
Set automationObject = addin.Object
automationObject.DisplayMessage
End Sub
From the VBA code, you can see that my add-in is registered as “ExcelAddinService”. All I’m doing is fetching the COMAddIn object that represents this particular add-in in the COMAddIns collection (specified by ProgId). Then, I’m fetching the Object property of this add-in, and invoking the exposed method. If you’re following along, joining the dots as it were, you can infer that the VSTO runtime takes the return value from the RequestComAddInAutomationService method in the add-in to set the COMAddIn::Object property to the instance of the AddinUtilities class defined in the add-in.
Note that my VBA is using late binding – I don’t have a reference to the add-in at design-time. If the add-in exposed many methods with varying signatures, it might be worth adding a reference to it in the VBA editor.
So, to show this, I’ll expand my add-in to expose a second method, this one takes a couple of parameters and uses them to interact with the active Worksheet.
[ComVisible(true)]
[Guid("B523844E-1A41-4118-A0F0-FDFA7BCD77C9")]
[InterfaceType(ComInterfaceType.InterfaceIsDual)]
public interface IAddinUtilities
{
void DisplayMessage();
void SetCellValue(String cellAddress, object cellValue);
}
[ComVisible(true)]
[ClassInterface(ClassInterfaceType.None)]
public class AddinUtilities : IAddinUtilities
{
public void DisplayMessage()
{
MessageBox.Show("Hello World");
}
public void SetCellValue(String cellAddress, object cellValue)
{
Excel.Worksheet sheet = (Excel.Worksheet)
Globals.ThisAddIn.Application.ActiveSheet;
Excel.Range cell = sheet.Cells.get_Range(
cellAddress, Type.Missing);
cell.Value2 = cellValue;
}
}
In my VBA, I can add a reference to the AddinService add-in, so that I get design-time intellisense. I’ll put a second CommandButton on the worksheet, and code it to call the SetCellValue method:
Private Sub CommandButton2_Click()
Dim addin As Office.COMAddIn
Dim utilities As ExcelAddinService.addinUtilities
Set addin = Application.COMAddIns("ExcelAddinService")
Set utilities = addin.Object
Call utilities.SetCellValue("a1", 456.78)
End Sub
I can even write a Windows Forms app to automate Excel externally, and invoke the exposed add-in methods. For example, this is a Windows Forms app with 2 buttons – I launch Excel when the form loads, and cache the Application and the add-in Object. Then, I call one of the exposed methods in each button Click handler:
public partial class WinTestAddinServiceForm : Form
{
public WinTestAddinServiceForm()
{
InitializeComponent();
}
private Excel.Application excel;
private ExcelAddinService.IAddinUtilities utils;
private void WinTestAddinServiceForm_Load(object sender, EventArgs e)
{
// Launch Excel, make it visible, and ensure there is
// at least one sheet.
excel = new Excel.Application();
excel.Visible = true;
excel.Workbooks.Add(Excel.XlSheetType.xlWorksheet);
// Fetch the add-in we want to exercise, and cache
// its exposed object.
object addinName = "ExcelAddinService";
Office.COMAddIn addin = excel.COMAddIns.Item(ref addinName);
utils = (ExcelAddinService.IAddinUtilities)addin.Object;
}
private void WinTestAddinServiceForm_FormClosed(
object sender, FormClosedEventArgs e)
{
// Clean up all Excel object references.
utils = null;
excel = null;
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
private void displayMessage_Click(object sender, EventArgs e)
{
utils.DisplayMessage();
}
private void setCellValue_Click(object sender, EventArgs e)
{
utils.SetCellValue("a1", 123.45);
}
}
As you can see, I’ve obviously got a reference to the ExcelAddinService assembly in this project – that’s only so that I can use the IAddinUtilities interface. Realistically of course, the interface would be best defined in an assembly separate from the add-in.
This feature seems like a small thing, but actually its part of a wider strategy to support the idea of Office as a true development platform. That idea implies at least some minimal level of interconnectivity support between various custom pieces in a solution.