使用COM Interop将对象从C＃传递到VBA

Is it possible to pass a custom object (like MyClass[]) from C# to VBA using COM?

是否可以使用COM将自定义对象（如MyClass []）从C＃传递给VBA？

If not, which is the best solution to get this working?

如果没有，哪个是最好的解决方案让这个工作？

1 个解决方案

#1

I assume you're talking about Excel VBA to C# ...

我假设您正在谈论Excel VBA到C＃...

here's a minimal C# class that does it, in a project w default name ClassLibrary1:

这是一个最小的C＃类，在项目中使用默认名称ClassLibrary1：

using System;
using System.Runtime.InteropServices;

namespace Tester
{
    [ClassInterface(ClassInterfaceType.AutoDual)]
    public class TestClass
    {
        public double D { get; set; }  // simple property to get, set a double
        public string S { get; set; }  // simple property to get, set a string
    }
}

and here's VBA to try the class out:

这是VBA尝试上课：

Private Sub foo()

Dim X As New ClassLibrary1.TestClass

X.S = "Hello"
Debug.Print X.S ' prints "hello"

X.D = 12
Debug.Print X.D ' prints a 12

End Sub

and here are the extra things you need to do to make this work:

以下是使这项工作需要做的额外事情：

(1) in C# Project...Properties...Build ==> check "Register for COM interop
(2) in C# Project...Properties...Application...Assembly Information ==> 
    check "Make assembly COM-visible"
(3) in VBA ... Tools ... References, browse to the C# bin output directory and select the "*.tlb" file

Note: this scheme may fail depending on what you add to the class - I don't think VBA will "see" static classes or classes w other than default constructors. You also cannot map VB collections to .NET collections, but you will be able to pass basic types (double, long) and arrays of the basic types back and forth. Also - the "Autodual" option used is a cheap way to get methods exposed ... easy to get started but less efficient and exposes all public methods. Better practice (but more work) would be to set up your own interfaces. If you expand the members of this TestClass to include instances of other classes you have defined, and if you likewise expose those class methods via AutoDual or via hand-coded interfaces, then those classes and their (non-overloaded) methods will likewise be visible in VBA (with Intellisense).

注意：此方案可能会失败，具体取决于您添加到类中的内容 - 我认为VBA不会“看到”除默认构造函数之外的静态类或类。您也无法将VB集合映射到.NET集合，但您可以来回传递基本类型（double，long）和基本类型的数组。此外 - 使用的“Autodual”选项是一种廉价的方法来暴露方法...易于上手但效率较低，并暴露所有公共方法。更好的实践（但更多的工作）是建立自己的界面。如果展开此TestClass的成员以包含已定义的其他类的实例，并且如果同样通过AutoDual或手动编码接口公开这些类方法，则这些类及其（非重载）方法同样可见在VBA（使用Intellisense）。

Hope this helps.

希望这可以帮助。

#1