Use CLR classes from COM addin in Excel VBA?

Question

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

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

I would try Set tc = automationObject.TestClass... Or Dim tc As New automationObject.TestClass. — FaneDuru
– FaneDuru, Commented Jul 15, 2020 at 14:15
In VBA there are two types of bindings 1) Early Binding : where an object type is determined by the compiler 2) Late Binding : Where the object type is determined by header info at run time. You are having issues with VBA recognizing the late binding type at runtime. See : learn.microsoft.com/en-us/visualstudio/vsto/late-bindin — jdweng
– jdweng, Commented Jul 15, 2020 at 14:32
Take a look in the object browser. This will allow you to identfy the name and class you should use when using new. — freeflow
– freeflow, Commented Jul 15, 2020 at 15:27
@FaneDuru Then I get the compilation error of "User defined type not defined". — Ralf Stauder
– Ralf Stauder, Commented Jul 16, 2020 at 11:34
@jdweng Your link is broken, but I found learn.microsoft.com/en-us/visualstudio/vsto/… which I assume is what you meant? But how will that article help me, it talks about these bindings in regular VB (or C#) projects, which is not VBA. To clarify: I have my custom class in my (C#) addin, but I want to access it within an Excel VBA macro. — Ralf Stauder
– Ralf Stauder, Commented Jul 16, 2020 at 11:37

Jeremy Thompson · Accepted Answer · 2020-07-17 04:50:17Z

3

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()

edited Jul 17, 2020 at 4:50

Jeremy Thompson

66.3k38 gold badges225 silver badges344 bronze badges

answered Jul 17, 2020 at 2:18

Dmitry Streblechenko

67.1k4 gold badges56 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Jeremy Thompson Over a year ago

How would OP go about changing their code? Guessing wrap an Attribute over TestClass? I know you're right, I'd like to learn from this how it's done.

Dmitry Streblechenko Over a year ago

It is exactly the same as Utilities class - define an interface, have a class that implements it and is marked with the ComVisible attribute, etc.

Ralf Stauder Over a year ago

Thanks a lot, that did answer the question in regards to the example I had given... Any additional pointers, though, to what I could do (other than complete wrappers) if I didn't just want to pass on my own "TestClass", but an object of an external library, which I cannot just wrap an interface around and slap "ComVisible" to? ^^

Dmitry Streblechenko Over a year ago

Wrapping it as your own interface is pretty much the only thing you can do.

Collectives™ on Stack Overflow

Use CLR classes from COM addin in Excel VBA?

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related