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

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://docs.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.

RequestComAddInAutomationService

RequestComAddInAutomationService

private ThisAddIn utilities;

protected override object RequestComAddInAutomationService()

if (utilities == null)
utilities = new ThisAddIn();

return utilities;



1 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:

IThisAddIn

[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.

Application.COMAddIns

AssemblyTitle

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

Set

Set MyNoNMissingObject = ExcelAddIn1.Object

MyNoNMissingObject.GetText()

[InterfaceType(ComInterfaceType.InterfaceIsDual)]

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.