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

问题描述

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

如果不是，这是获得这个的最佳解决方案工作？

解决方案

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

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

  using System; 
使用System.Runtime.InteropServices; 
 
命名空间Tester 
 {
 [ClassInterface（ClassInterfaceType.AutoDual）] 
 public class TestClass 
 {
 public double D {get;组; } //简单属性获取，设置一个double 
 public string S {get;组; } //简单属性获取，设置一个字符串
} 
} 
  

并且这里是VBA尝试这个课程：

  Private Sub foo（）
 
 Dim X As New ClassLibrary1.TestClass 
 
 XS =Hello
 Debug.Print XS打印hello
 
 XD = 12 
 Debug.Print XD'一个12 
 
 End Sub 
  

这里是你需要的额外的东西做这个工作：

 （1）在C＃项目...属性... Build ==>检查注册COM Interop 
（2）在C＃项目...属性...应用程序...程序集信息==> 
检查使程序集COM可见
（ 3）在VBA ...工具...参考，浏览到C＃bin输出目录并选择* .tlb文件
  

注意：这个方案可能会失败，取决于你添加到类中 - 我不认为VBA会看到除了默认构造函数之外的静态类或类，你也不能将VB集合映射到.NET集合，但是您可以传递基本类型（双，长）和数组的基本类型，而且 - 所使用的Autodual选项是一种廉价的方式来暴露方法...很容易得到开始但效率较低并暴露所有公共方法更好的做法（但更多的工作）将是设置自己的接口如果您扩展该TestClass的成员以包括您定义的其他类的实例，并且如果同样暴露通过AutoDual或通过手工编码的接口，那些类和它们的（非重载）方法同样可以在VBA（使用Intellisense）中看到。

希望这有帮助。 >

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?

解决方案

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

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

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

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

Hope this helps.

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