Invoking a COM add-in from an Excel XLL add-in: advanced sample

Really often, when I saw an error returned by an Excel formula, I thought about the poor possibilities that this error-reporting approach – a remnant of bygone concepts – provides for developers. The very first time I thought about showing a custom task pane from a UDF was when Add-in Express allowed showing custom panes in Excel; it was back in 2007. This blog is the result of those old thoughts combined happily with a brief visit of what is probably called inspiration. Sweet moments…

So, what’s this post about?

How to let your XLL provide extended error information for the end-user:

• How to create a COM add-in and XLL in the same assembly
• How to prevent an Advanced Excel task pane form showing at add-in startup
• How to create and show an Advanced Excel task pane at run-time
• How to get a COM add-in with a given ProgID in the Office.COMAddins collection
• How to call a public method that a COM add-in provides using the Office.COMAddin class
• How to check if the argument of an XLL function is missing, is null or is an Excel-specific error value
• How an XLL returns an Excel error value
• How to use the SendMessage() method and the OnSendMessage event of the ADXAddinModule class to delay code execution

In How to use Evaluate to invoke an Excel UDF programmatically, I explained how you invoke an XLL UDF from a .NET based COM add-in. Now I’ll talk about accessing the COM add-in from an Excel XLL add-in; the COM add-in and the XLL add-in inhabit the same assembly, as described in HowTo: Create a COM add-in, XLL UDF and RTD server in one assembly.

Creating the UI of a COM add-in

In a COM add-in project targeting Excel, create this Ribbon UI (find instructions in Building Office COM add-ins):

To support this UI, write this code (you can download C# and VB.NET projects at the end of the post):

C#:

bool isCheckBoxTicked = false;
private void showPaneCheckBox_OnClick(object sender, IRibbonControl control, bool pressed)
{
    isCheckBoxTicked = pressed;
}
// if IsCheckBoxTicked is false, the pane will not show
public bool IsCheckBoxTicked
{
    get { return isCheckBoxTicked;}
}

Then add an Advanced Excel Task Pane (see how to do this in Building an advanced Excel task pane), drop a label onto it and write this method updating the label:

public void SetText(string text)
{
    this.label1.Text = text;
}

Manipulating the visibility of an Advanced Excel task pane

In addition, to prevent the pane from showing when the Ribbon checkbox above is cleared (in particular, it is unchecked when the add-in starts), handle the ADXBeforeTaskPaneShow event of the pane class and set the pane’s visibility as follows:

private void MyTaskPane_ADXBeforeTaskPaneShow(object sender, ADXBeforeTaskPaneShowEventArgs e)
{
    this.Visible = MyAddin96.AddinModule.CurrentInstance.IsCheckBoxTicked;
}

Now, in the add-in module (it represents the COM add-in), add this method:

public void ShowPane(string message)
{
    MyTaskPane myPane = (MyTaskPane)myTaskPanesCollectionItem.TaskPaneInstance;
    if (myPane == null) myPane =
         (MyTaskPane)myTaskPanesCollectionItem.CreateTaskPaneInstance();
    myPane.SetText(message);
    myPane.Show(); // will show up only if IsCheckBoxTicked is true
}

Creating an XLL

Now, add an XLL add-in module to the COM add-in project: open the Add New Item dialog, select the ADX XLL Module in the Extensibility branch and click OK.

In the XLLContainer class defined in the XLL module, create this function:

public static object MyFunc(object arg)
{
    if (Module.IsInFunctionWizard) return "";
    ADXExcelRef caller =
        Module.CallWorksheetFunction(ADXExcelWorksheetFunction.Caller) as ADXExcelRef;
    string callerAddress = caller.ConvertToA1Style();
    string message = "The call originated from " + callerAddress + ". ";
    if (arg == null)
    {
        // …
        return AddinExpress.MSO.ADXExcelError.xlErrorNull;
    }
    else if (arg is AddinExpress.MSO.ADXExcelError)
    {
        // …
        return AddinExpress.MSO.ADXExcelError.xlErrorValue;
    }
    else if (arg is System.Reflection.Missing)
    {
        // …
        return AddinExpress.MSO.ADXExcelError.xlErrorNA;
    }
    return "OK";
}

The function starts with determining if it is called from the Insert Function wizard. On the next step, it determines the caller address. Note that the ConvertToA1Style() method is not thread-safe, but see Thread-safe XLL. How to get the caller address.

Finally, let’s show the pane from the XLL.

Calling a method defined in a COM add-in from the XLL add-in

The three scenarios below demonstrate how you invoke the ShowPane method in three ways.

Invoke a method in a COM add-in using the COMAddins collection.

message += "The parameter is null.";
Office.COMAddIns comAddins = Module.ExcelApp.COMAddIns;
object comAddinProgId = "MyAddin96.AddinModule";
Office.COMAddIn comAddin = comAddins.Item(comAddinProgId);
if (comAddin != null)
{
    object comAddinObj = comAddin.Object;
    if (comAddinObj != null)
    {
        try
        {
            comAddinObj.GetType().InvokeMember("ShowPane",
                System.Reflection.BindingFlags.InvokeMethod,
                null, comAddinObj, new string[] { message });
        }
        catch { }
        Marshal.ReleaseComObject(comAddinObj);
    }
    Marshal.ReleaseComObject(comAddin);
}

The code fragment above accesses the COMAddins collection that contains all COM add-ins available in Excel. Then it gets the item corresponding to your COM add-in. To find that item, the ProgId of the COM add-in is used; see the ProgID attribute of the add-in module (What is ProgId?). Then, you reach the add-in module (via object comAddinObj = comAddin.Object;) and use late binding (Type.InvokeMemeber) to call the ShowPane method and pass it a string parameter. After the call, you release all previously created COM objects and let the UDF return an Excel error value. Are you with me?

Just FYI, this is the only approach to invoke a COM add-in from an external application.

Invoke a method in the COM add-in directly

When a COM add-in and XLL add-in reside in the same assembly and, more importantly, they inhabit the same AppDomain, you can just call a method defined in the COM add-in. In this sample the code is:

message += "The parameter is an Excel error value.";
MyAddin96.AddinModule theComAddin = MyAddin96.AddinModule.CurrentInstance;
if (theComAddin != null)
{
    theComAddin.ShowPane(message);
}

That is, you get the current instance of the add-in module and invoke the method in question. Really simple!

Invoke a method in the COM add-in via SendMessage (suggested!)

An XLL UDF is for calculations only; it isn’t expected to interact with the UI things: show message boxes, panes and the like. That is, by showing a pane when the XLL is being called, we break Excel rules. Of course, we can believe that Excel architects foresaw this possibility and showing our pane will not crash Excel, but, anyway, it will be wise to show the pane after the XLL completes. Now how do you do this?

The solution is: you pass a custom message to the SendMessge() method of the add-in module and then handle the OnSendMessage event of the add-in module to perform required actions when that message is received.

The trick is based on the fact that Add-in Express creates a hidden window for your COM add-in and when you call ADXAddinModule.SendMessge(), your message is actually sent to that hidden window. Then the execution of the caller continues and completes. When Excel gets ready to process the message queue of the hidden window, your message is extracted and passed to the OnSendMessage event of the add-in module. The delay between sending and receiving the message allows your XLL function to perform its tasks. As the result, the pane is shown when Excel is prepared to process drawing requests!

Pay attention to the fact that OnSendMessage always occurs on the main thread. BTW, do you remember that no Office object models are thread-safe? Do you remember that you can use any object model in the main thread only? You may want to re-read On using threads in managed Office extensions.

Now, let’s look at the code.

In the function:

message += "The parameter is missing.";
Module.Messages.Add(message);
MyAddin96.AddinModule theComAddin = MyAddin96.AddinModule.CurrentInstance;
if (theComAddin != null)
    theComAddin.SendMessage(theComAddin.WM_ShowMyPane);

Module.Messages above is an ArrayList that stores error messages.

And in the add-in module:

private const int WM_USER = 1024;
public int WM_ShowMyPane = WM_USER + 1800;
private void AddinModule_OnSendMessage(object sender, ADXSendMessageEventArgs e)
{
    if (e.Message == WM_ShowMyPane)
    {
        XLLModule theXLL = MyAddin96.XLLModule.CurrentInstance;
        if (theXLL != null)
        {
            if (theXLL.Messages.Count > 0)
            {
                string message = theXLL.Messages[theXLL.Messages.Count - 1].ToString();
                ShowPane(message);
            }
        }
    }
}

theXLL.Messages above is the ArrayList defined in the XLL module (see the previous code fragment).

Here’s an example of the resulting message:

Now I wonder if you have any thoughts on this?

Available downloads:

This sample XLL UDF was developed using Add-in Express for Office and .net:
C# sample project
VB.NET sample project

You may also be interested in:

Creating Excel XLL add-ins
Excel UDF tips and tricks