从 vba 调用 .net 库方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19954001/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Calling a .net library method from vba
提问by user1012598
I have developed a web service in ASP.net, c#, and hosted on IIS, which is to be consumed by a vba client. Having downloaded the Office 2003 Web Services 2.01 Toolkit, I encountered a problem in successfully creating the proxy classes required (as documented by many users online), and decided to create a .net dll library instead. I have created the library, which references the web service and exposes one of its methods to a public function in c#.
我在 ASP.net、c# 中开发了一个 Web 服务，并托管在 IIS 上，该服务将由 vba 客户端使用。下载了 Office 2003 Web 服务 2.01 工具包后，我在成功创建所需的代理类时遇到了问题（如许多在线用户所记录），因此决定改为创建 .net dll 库。我创建了库，它引用了 Web 服务并将其方法之一公开给 c# 中的公共函数。
I now have three questions:
我现在有三个问题：
How do I reference the dll class in VBA? I tried to go to Tools->References and browsed to the dll location, but I get the error "can't add reference to the file specified". Is there a specific location on the disk I have to have the .dll copied?
Can I also copy the dll.config file next to the dll file, so as to have the endpoint url there?
Since the method to call is accepting an object (consisting of various members and a couple of List<> members, how are these to be implemented in VBA code?
如何在 VBA 中引用 dll 类？我尝试转到“工具”->“引用”并浏览到 dll 位置，但出现错误“无法添加对指定文件的引用”。磁盘上是否有特定位置我必须复制 .dll？
我也可以复制 dll 文件旁边的 dll.config 文件，以便在那里有端点 url 吗？
由于要调用的方法正在接受一个对象（由各种成员和几个 List<> 成员组成，如何在 VBA 代码中实现这些？
回答by rory.ap
You will need to make a COM-callable wrapper (CCW) for your assembly (DLL). .NET interoperability is a fairly in-depth topic, but it's relatively easy to get something off the ground.
您需要为程序集 (DLL) 制作一个 COM 可调用包装器 (CCW)。.NET 互操作性是一个相当深入的主题，但要开始工作相对容易。
First of all, you need to make sure your entire assembly is registered for COM interop. You can do this on the "Build" tab in Visual Studio by checking "Register for COM Interop". Secondly, you should include the System.Runtime.InteropServices in all your classes:
首先，您需要确保您的整个程序集已针对 COM 互操作注册。您可以通过选中“注册 COM Interop”在 Visual Studio 的“构建”选项卡上执行此操作。其次，您应该在所有类中包含 System.Runtime.InteropServices：
using System.Runtime.InteropServices;
Next, you should decorate all the classes you want to be exposed with the [Serializable(), ClassInterface(ClassInterfaceType.AutoDual), ComVisible(true)]attributes. This will make it so you can access the class members properly and using intellisense from within the VBA editor.
接下来，您应该用[Serializable(), ClassInterface(ClassInterfaceType.AutoDual), ComVisible(true)]属性装饰所有要公开的类。这将使您可以正确访问类成员并在 VBA 编辑器中使用智能感知。
You need to have an entry point -- i.e. a main class, and that class should have a public constructor with no arguments. From that class, you can call methods which return instances of your other classes. Here is a simple example:
您需要有一个入口点——即一个主类，并且该类应该有一个没有参数的公共构造函数。从该类中，您可以调用返回其他类实例的方法。这是一个简单的例子：
using System;
using System.Collections.Generic;
using System.Runtime.InteropServices;
using System.Linq;
using System.Text;
using System.Windows.Forms;
namespace MyCCWTest
{
[Serializable(), ClassInterface(ClassInterfaceType.AutoDual), ComVisible(true)]
public class Main
{
public Widget GetWidget()
{
return new Widget();
}
}
[Serializable(), ClassInterface(ClassInterfaceType.AutoDual), ComVisible(true)]
public class Widget
{
public void SayMyName()
{
MessageBox.Show("Widget 123");
}
}
}
Once you compile your assembly, you should be able to include a reference to it within VBA by going to "Tools > References":
编译程序集后，您应该能够通过转到“工具 > 引用”在 VBA 中包含对它的引用：
Then you should be able to access your main class and any other classes like this:
然后你应该能够访问你的主类和任何其他类，如下所示：
Sub Test()
Dim main As MyCCWTest.main
Set main = New MyCCWTest.main
Dim myWidget As MyCCWTest.Widget
Set myWidget = main.GetWidget
myWidget.SayMyName
End Sub
To answer your question about List<>: COM doesn't know anything about generics, so they're not supported. In fact, using arrays in CCW's is even a tricky subject. In my experience, I've found the easiest thing to do is to create my own collection classes. Using the example above, I could create a WidgetCollection class. Here is a slightly-modified project with the WidgetCollection class included:
回答您关于 List<> 的问题：COM 对泛型一无所知，因此不支持它们。事实上，在 CCW 中使用数组甚至是一个棘手的主题。根据我的经验，我发现最简单的方法是创建自己的集合类。使用上面的示例，我可以创建一个 WidgetCollection 类。这是一个稍微修改的项目，其中包含 WidgetCollection 类：
using System;
using System.Collections;
using System.Collections.Generic;
using System.Runtime.InteropServices;
using System.Linq;
using System.Text;
using System.Windows.Forms;
namespace MyCCWTest
{
[Serializable(), ClassInterface(ClassInterfaceType.AutoDual), ComVisible(true)]
public class Main
{
private WidgetCollection myWidgets = new WidgetCollection();
public Main()
{
myWidgets.Add(new Widget("Bob"));
myWidgets.Add(new Widget("John"));
myWidgets.Add(new Widget("Mary"));
}
public WidgetCollection MyWidgets
{
get
{
return myWidgets;
}
}
}
[Serializable(), ClassInterface(ClassInterfaceType.AutoDual), ComVisible(true)]
public class Widget
{
private string myName;
public Widget(string myName)
{
this.myName = myName;
}
public void SayMyName()
{
MessageBox.Show(myName);
}
}
[Serializable(), ClassInterface(ClassInterfaceType.AutoDual), ComVisible(true)]
public class WidgetCollection : IEnumerable
{
private List<Widget> widgets = new List<Widget>();
public IEnumerator GetEnumerator()
{
return widgets.GetEnumerator();
}
public Widget this[int index]
{
get
{
return widgets[index];
}
}
public int Count
{
get
{
return widgets.Count;
}
}
public void Add(Widget item)
{
widgets.Add(item);
}
public void Remove(Widget item)
{
widgets.Remove(item);
}
}
}
And you can use it like this in VBA:
你可以在 VBA 中像这样使用它：
Sub Test()
Dim main As MyCCWTest.main
Set main = New MyCCWTest.main
Dim singleWidget As MyCCWTest.Widget
For Each singleWidget In main.myWidgets
singleWidget.SayMyName
Next
End Sub
NOTE: I have included System.Collections;in the new project so my WidgetCollection class can implement IEnumerable.
注意：我已包含System.Collections;在新项目中，因此我的 WidgetCollection 类可以实现 IEnumerable。
