在 Excel VBA 中使用 COM 插件中的 CLR 类? [英] Use CLR classes from COM addin in Excel VBA?

问题描述

我有一个 Excel VSTO COM 插件，我可以在 Excel VBA 中成功加载引用.我可以交换基本类型(如字符串)，但我也可以在 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?

假设我在 C# 中有这个类:

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
}

...并且我的插件向 Excel 提供了此类的对象:

...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();
}

所以一切都是非常默认的 VSTO-tutorial-stuff，除了那个类.

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

现在，当我在 Excel VBA 块中(例如在ThisWorksheet"中)使用我的插件时，通过定义这样的函数:

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

...然后 A1 已按预期正确设置，并且我可以一直调试到最后一行(因此获取 tc 的对象仍然有效！)，但是在最后一行我收到错误需要对象".我可以看到 tc 是 Variant/Object 类型，所以我认为 VBA 不知道如何处理它.有什么办法

...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. 告诉 VBA 这个类的样子
2. 实际上让它工作，以便在 VBA 中调用 tc 上的函数调用我的 .Net 库中的正确代码?

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?

推荐答案

TestClass 类必须像您的 Utilities 类一样实现 - 它必须实现一个公共的 IDispatch 派生接口.

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;
}

现在 VBA 调用有一个类接口到诸如 GetTextFunc 之类的方法:

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

这篇关于在 Excel VBA 中使用 COM 插件中的 CLR 类?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！