Call Excel Add-In function in macro

Question

I am developing Add-in for Excel 2013 and I have created a function in Excel Add-In as below

  public string ExcelReturnString()
    {
        return "This is the string: hi";
    }

I have used below code to call the function, but it throws an error.

Application.Run(ExcelReturnString)

How can I call the Add-in function in macro?

Answer 1

This is about the farthest thing from straight-forward, but this is how you accomplish the task. I'm going to be as explicit as possible, because the first two or three times I tried to do this, I missed a LOT.

First, when you create the class that hosts ExcelReturnString(), you need to decorate the class with an interface that has the following attributes and then also tag the attributes for each method you want to expose. I made the add-in class "TestExcelAddIn" for the sake of this example:

using System.Data;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;

namespace TestExcelAddIn
{
    [ComVisible(true)]
    [InterfaceType(ComInterfaceType.InterfaceIsDual)]
    public interface IStringGetter
    {
        string ExcelReturnString();
    }

    [ComVisible(true)]
    [ClassInterface(ClassInterfaceType.None)]
    public class StringGetter : IStringGetter
    {
        public string ExcelReturnString()
        {
            return "This is the string: hi";
        }
    }
}

Then, in the main class, associated with "Excel" in your project, you have to override RequestComAddInAutomationService in the following manner. Again, I am including EVERYTHING so you know which class is which (I didn't when I first read it).

namespace TestExcelAddIn
{
    public partial class ExcelTest
    {
        private StringGetter myAddIn;

        protected override object RequestComAddInAutomationService()
        {
            if (myAddIn == null)
                myAddIn = new StringGetter();

            return myAddIn;
        }

        private void ThisAddIn_Startup(object sender, System.EventArgs e)
        {
        }

        private void ThisAddIn_Shutdown(object sender, System.EventArgs e)
        {
        }

        #region VSTO generated code
        #endregion
    }
}

Now VBA is ready to consume this method in the following manner:

Sub Test()

    Dim addin As Office.COMAddIn
    Dim automationObject As Object
    Dim returnString As String

    Set addin = Application.COMAddIns("TestExcelAddIn")
    Set automationObject = addin.Object

    returnString = automationObject.ExcelReturnString

End Sub

You could have given me 100 years to figure this out, and I would not have. Actually credit MSDN for the Rosetta stone on it:

https://msdn.microsoft.com/en-us/library/bb608621.aspx?f=255&MSPPError=-2147217396