I'm using VSTO to make a bunch of functions written in C# accessible from Excel VBA. It would be convenient to create types (say, a class) with a number of properties to allow me to pass that single class to and from excel.
VSTO overrides the RequestComAddInAutomationService method to expose a single class which contains the methods I want to expose. I therefore cannot figure out how to expose a second class or struct etc. My class uses an interface to expose its methods to vba, but you cannot define types in an interface. I have tried to create the type class within my main class, but am unable to get vba to recognise it.
This is the code inside "ThisAddIn.cs:
public partial class ThisAddIn
{
private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
}
private void ThisAddIn_Shutdown(object sender, System.EventArgs e)
{
}
#region VSTO generated code
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InternalStartup()
{
this.Startup += new System.EventHandler(ThisAddIn_Startup);
this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown);
}
#endregion
private AddInUtilities utilities;
protected override object RequestComAddInAutomationService()
{
if (utilities == null) utilities = new AddInUtilities();
return utilities;
}
}
And in AddInUtilities.cs:
[ComVisible(true)] public interface IAddInUtilities { string[] ListKeyMetrics(); string[] ListSetptMetrics(); AddInUtilities.testStruct myTest(); double test { get; set; } }
[ComVisible(true)]
[ClassInterface(ClassInterfaceType.None)]
public class AddInUtilities : IAddInUtilities
{
public class testStruct
{
private double _x;
private double _y;
public testStruct(int z)
{
_x = 0;
_y = 0;
}
public double x
{
get { return _x; }
set { _x = value; }
}
public double y
{
get { return _y; }
set { _y = value; }
}
}
public double test
{
get; set;
} = 5;
public string[] ListKeyMetrics()
{
string[] ret = { "SCz", "SCx", "etc" };
return ret;
}
public string[] ListSetptMetrics()
{
string[] ret = { "FRH", "RRH", "etc" };
return ret;
}
public testStruct myTest()
{
testStruct ret = new testStruct(0);
ret.x = 1;
ret.y = 2;
return ret;
}
}
Any suggestions on how I can create a type in VSTO and have it visible from vba?
