Creating a callable function in a VSTO office addin - COMAddIns.Object is always Nothing

Question

I want to be able to call a function in a VSTO addin from an Excel VBA macro. To test the principle I have the following C# code.

namespace ExcelAddIn1
{
    [ComVisible(true)]
    public interface IThisAddIn
    {
        String GetText();
    }

    [ComVisible(true)]
    [ClassInterface(ClassInterfaceType.None)]
    public partial class ThisAddIn : IThisAddIn
    {    
        public String GetText()
        {
            return "Now is the winter of dicontent made glorius summer by this son of York";
        }       
    }
}

And I have the following VBA script

Public Sub RunTest()
    Dim txt As String
    Dim AddInList As Object
    Dim ExcelAddIn1 As COMAddIn
    Dim ThisAddIn As Object

    Set ExcelAddIn1 = Application.COMAddIns("ExcelAddIn1")

    txt = ExcelAddIn1.Object.GetText()
    Sheets(1).Cells(2, 1).Value = txt
End Sub

ExcelAddIn1.Object = Nothing

The Microsoft walkthrough at https://learn.microsoft.com/en-gb/visualstudio/vsto/walkthrough-calling-code-in-a-vsto-add-in-from-vba seems to be a bit muddled.

I think I might need to add RequestComAddInAutomationService but it doesn't work as described in the walkthrough. When I try to instantiate an instance of my class within RequestComAddInAutomationService I get the error basically telling me I need to supply an ApplicationFactory and IServiceProvider as parameters.

private ThisAddIn utilities;

protected override object RequestComAddInAutomationService()
{
    if (utilities == null)
       utilities = new ThisAddIn();

    return utilities;
}

Answer 1

The class derived from IThisAddIn must be different of the main vsto class (as in the microsoft example) so just replace your code with:

  [ComVisible(true)]
    public interface IComAddIn
    {
        String GetText();
    }

    [ComVisible(true)]
    [ClassInterface(ClassInterfaceType.None)]
    public  class AddInUtilities : IComAddIn
    {
        public String GetText()
        {
            return "Now is the winter of dicontent made glorius summer by this son of York";
        }
    }

in vsto:

    public partial class ThisAddIn
    {
        private AddInUtilities utilities;

        protected override object RequestComAddInAutomationService()
        {
            if (utilities == null)
                utilities = new AddInUtilities();

            return utilities;
        }
  ....

Also when you call Application.COMAddIns in VBA, be sure that the string you give as argument correspond to the AssemblyTitle of your vsto project.

PS there is no need to "register for com interop".