Can I use Excel-DNA for Excel adding (dll instead of xll)?

Oct 29, 2013 at 8:29 PM
Hi,

I created my UDF using an Excel Addin that implements IDTExtensibility2.

I want to be able to provide description for all Interface functions and each of its parameters.

I noticed two differences between my Excel Addin and the Excel addin provided in the Excel DNA documentation/code sample:

In my case,
  • the output is an Excel Addin dll.
  • the UDF are defined through a public interface, and all the exposed functions are not static.
In the documentation for Excel DNA:
  • the output in an xll file,
  • the UDF functions are defined as static,
Can I still use Excel DNA in my case? Is there a different approach from the xll approach when dealing with my type of project?

Here is an abstract of my Excel Addin, in case it helps.

using System;
using System.ComponentModel;
using System.Runtime.InteropServices;
using System.Windows.Forms;
using Microsoft.Office.Core;
using Excel = Microsoft.Office.Interop.Excel;
using ExcelDna.Integration;


namespace CustomUDFs
{
public interface IBridge
{
    [ExcelFunction(Description = "Returns a test by passing a string")]
    string itest(string param1);

}

[ComDefaultInterface(typeof(IBridge))]
[Guid("181F3826-7719-4C26-ADE8-CDBF9B0B3343")]
public class Bridge : ExtensibilityBase, IBridge
{
    [ExcelFunction(Description = "Returns a test by passing a string")]
    public static string itest(
         string param1
        )
    {
        MessageBox.Show("this is a test");
        return "";
    }
}
}


Thank you for your help

G.
Coordinator
Oct 29, 2013 at 10:26 PM
Hi G,

You've made an Automation Add-in, which is a different approach to the native SDK integration approach taken by Excel-DNA.
You can't easily mix the approaches.

You'll need to change your .dll to export public static functions, then define a .dna file and use the .xll if you want to go the Excel-DNA route.
Is this a problem?

-Govert
Oct 30, 2013 at 12:59 AM
Hi Govert,

Thank you for getting back to me so quickly.

Here is a bit of background in what I am trying to achieve.

I have an Excel Com-Addin (called "MYCOMAddin") that implements pretty complex Excel automation functionality. It is written in C#.

I want to expose some of that functionality thru UDFs so that an end user can exercise my Com Addin functionality directly from an Excel formula, without the need of macros.

So I decided to create an Excel Addin, called Bridge, to act as a bridge, between Excel, and the Com Addin Public interface.

Each public interface in the Bridge, would invoke the ComAddin, and exercise one of its public API.
Here is a code sample of the Bridge Excel Addin, demonstrating how I invoke the COM Addin from the Bridge for the function "RefreshAllRequests".

The code currently works very well, I am able to invoke the RefreshAllRequest COM Addin Interface from an excel formula. But, I also want to display description for the function and its parameters.
I tried ExcelApplication.MacroOptions, but I could not get it to work for my Excel Addin (code in the Bridge. I also tried to put the code in the Com Addin).

I initially tried to take the XLL approach to get to the same result (using Excel DNA), but I kept on getting blocked by the Static Public function in the bridge. From these functions, I could not get access to a properly initialized ExcelApplication object. And thus, I could not invoke the Com Addin.

That is why I took the DLL approach.
public interface IBridge
{
      [ExcelFunction(Description = "Refresh all requests in the workbook")]
      string RefreshAllRequests();
}

[ComDefaultInterface(typeof(IBridge))]
[Guid("181F3826-7719-4C26-ADE8-CDBF9B0B3343")]
public class Bridge : ExtensibilityBase, IBridge
{
    public string RefreshAllRequests()
    {
        //Call ARB.
        object addInObj = GetAddinObject(ExcelApplication.Application, "MYCOMAddin.Connect");

        if (addInObj != null)
        {
            try
            {
                object[] invokeArgs = new object[1];
                invokeArgs[0] = (object)ExcelApplication.Application.ActiveWorkbook;
                addInObj.GetType().InvokeMember("RefreshAllRequests", System.Reflection.BindingFlags.InvokeMethod, null, addInObj, invokeArgs);
                addInObj = null;
            }
            catch (Exception ex)
            {
                MessageBox.Show("Bridge: Cannot invoke Memnber: " + ex.Message);
            }
        }
        else
        {
            MessageBox.Show("Bridge: Cannot access Addin");
        }
        return "";
    }
   /// <summary>
    /// Retrieve the Excel Com Addin based on its string identifier. 
    /// </summary>
    /// <param name="excelApplication">The Excel Application Instance</param>
    /// <param name="addingID">The Com Addin string identifier</param>
    /// <returns>An Object representation of the Com Addin (null if the com add in was not found)</returns>
    private object GetAddinObject(Microsoft.Office.Interop.Excel.Application excelApplication, string addingID)
    {
        COMAddIn xlCOMAddIn = null;

        try
        {
            object ID = (object)addingID;
            xlCOMAddIn = excelApplication.COMAddIns.Item(ref ID);
        }
        catch (Exception ex)
        {
            System.Diagnostics.Debug.WriteLine("ErrorMessage :" + ex.Message);
            System.Diagnostics.Debug.WriteLine("StackTrace :" + ex.StackTrace);
        }
        if (xlCOMAddIn == null)
        {
            return null;
        }

        if (xlCOMAddIn.Connect == false)
        {
            try
            {
                xlCOMAddIn.Connect = true;
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine("ErrorMessage :" + ex.Message);
                System.Diagnostics.Debug.WriteLine("StackTrace :" + ex.StackTrace);
            }
        }

        object appAddin = null;

        try
        {
            appAddin = xlCOMAddIn.Object;
        }
        catch (Exception ex)
        {
            System.Diagnostics.Debug.WriteLine("ErrorMessage :" + ex.Message);
            System.Diagnostics.Debug.WriteLine("StackTrace :" + ex.StackTrace);
        }
        if (appAddin == null)
        {
            return null;
        }
        return appAddin;
    }