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?
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