使用 COM 互操作将对象从 C# 传递到 VBA [英] Passing objects from C# to VBA using COM Interop

问题描述

是否可以使用 COM 将自定义对象(如 MyClass[])从 C# 传递到 VBA?

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

如果没有，哪个是最好的解决方案?

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

推荐答案

我假设你在谈论 Excel VBA 到 C# ...

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

这是一个最小的 C# 类，在一个默认名称为 ClassLibrary1 的项目中:

here's a minimal C# class that does it, in a project w default name 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
    }
}

这里是 VBA 来试试这个类:

and here's VBA to try the class out:

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

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

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

希望这会有所帮助.

这篇关于使用 COM 互操作将对象从 C# 传递到 VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！