CODEWITHSUNDEEP
c#excelvbavsto
Ralf Stauder
Ralf Stauder

Reputation: 155

Use CLR classes from COM addin in Excel VBA?

I have an Excel VSTO COM addin, which I can successfully load an refer to in Excel VBA. I can exchange basic types (like strings), but can I also use complex classes from my addin in VBA?

Let's say I have this class in C#:

public class TestClass {
    public string GetTextFunc() => "check";
    public string TextProp => "doublecheck";
    public int RollDice() => 4; // chosen by fair dice roll
}

...and my addin provides an object of this class to Excel:

[ComVisible(true)]
public interface IUtilities {
    string GetString();
    TestClass GetTestClass();
}

[ComVisible(true)]
[ClassInterface(ClassInterfaceType.None)]
public class Utilities: IUtilities {
    public string GetString() => "Hello Excel";
    public TestClass GetTestClass() => new TestClass();
}

So everything is pretty default VSTO-tutorial-stuff, except for that class.

Now when I use my addin in the Excel VBA block (e.g. in "ThisWorksheet"), by defining a function like such:

Sub TestAddin()
    Dim addIn As COMAddIn
    Dim automationObject As Object
    Set addIn = Application.COMAddIns("MyExcelAddin")
    Set automationObject = addIn.Object
    
    ActiveSheet.Range("A1").Value2 = automationObject.GetString()
    
    Dim tc
    Set tc = automationObject.GetTestClass()
    ActiveSheet.Range("A2").Value2 = tc.GetTextFunc()
End Sub

...then A1 is correctly set, as expected, and I can debug-step all the way to the last line (so getting the object of tc still works!), but on the last line I get an error "Object required". I can see that tc is of type Variant/Object, so I assume VBA just doesn't know what to do with it. Is there any way to

  1. Tell VBA how this class looks like and
  2. actually have it work, so that calling a function on tc in VBA calls the correct code in my .Net library?

Upvotes: 3

Views: 619

Answers (1)

Dmitry Streblechenko
Dmitry Streblechenko

Reputation: 66356

TestClass class must be implemented just like your Utilities class - it must implement a public IDispatch-derived interface.

[ComVisible(true)]
public interface ITestClass {
    string GetTextFunc();
    string TextProp ();
    int RollDice();
}

[ComVisible(true)]
[ClassInterface(ClassInterfaceType.None)]
public class TestClass : ITestClass {
    public string GetTextFunc() => "check";
    public string TextProp => "doublecheck";
    public int RollDice() => 4;
}

Now the VBA call has a Class Interface to the methods such as GetTextFunc:

Dim tc
Set tc = automationObject.GetTestClass()
ActiveSheet.Range("A2").Value2 = tc.GetTextFunc()

Upvotes: 3

Related Questions