从 vba 调用 .net 库方法 [英] Calling a .net library method from vba
问题描述
我在 ASP.net、c# 中开发了一个 Web 服务，并托管在 IIS 上，供 vba 客户端使用.下载了 Office 2003 Web 服务 2.01 工具包后，我在成功创建所需的代理类时遇到了问题(如许多在线用户所记录)，因此决定改为创建 .net dll 库.我创建了库，它引用了 Web 服务并将其方法之一公开给 c# 中的公共函数.
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#.
我现在有三个问题:
如何在 VBA 中引用 dll 类?我尝试转到工具"->引用"并浏览到 dll 位置，但出现错误无法添加对指定文件的引用".磁盘上是否有特定位置我必须复制 .dll?
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?
我是否也可以复制dll文件旁边的dll.config文件，以便在那里有端点url?
Can I also copy the dll.config file next to the dll file, so as to have the endpoint url there?
由于调用的方法是接受一个对象(由各种成员和几个 List<> 成员组成，如何在 VBA 代码中实现这些?
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?
推荐答案
您需要为程序集 (DLL) 制作一个 COM 可调用包装器 (CCW)..NET 互操作性是一个相当深入的主题，但要开始工作相对容易.
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.
首先，您需要确保您的整个程序集已针对 COM 互操作注册.您可以通过选中注册 COM Interop"在 Visual Studio 的构建"选项卡上执行此操作.其次，您应该在所有类中包含 System.Runtime.InteropServices:
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:
using System.Runtime.InteropServices;
接下来，您应该使用 [Serializable(), ClassInterface(ClassInterfaceType.AutoDual), ComVisible(true)] 属性来修饰您想要公开的所有类.这将使您可以正确访问类成员并在 VBA 编辑器中使用智能感知.
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.
您需要有一个入口点——即一个主类，并且该类应该有一个没有参数的公共构造函数.从该类中，您可以调用返回其他类实例的方法.这是一个简单的例子:
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");
}
}
}
编译程序集后，您应该能够通过转到工具 > 引用"在 VBA 中包含对它的引用:
Once you compile your assembly, you should be able to include a reference to it within VBA by going to "Tools > References":
然后您应该能够访问您的主类和任何其他类，如下所示:
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
回答您关于 List<> 的问题:COM 对泛型一无所知，因此不支持它们.事实上，在 CCW 中使用数组甚至是一个棘手的主题.根据我的经验，我发现最简单的方法是创建自己的集合类.使用上面的示例，我可以创建一个 WidgetCollection 类.这是一个包含 WidgetCollection 类的稍微修改的项目:
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:
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);
}
}
}
你可以在 VBA 中像这样使用它:
And you can use it like this in 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
注意:我在新项目中包含了 System.Collections; 以便我的 WidgetCollection 类可以实现 IEnumerable.
NOTE: I have included System.Collections; in the new project so my WidgetCollection class can implement IEnumerable.
这篇关于从 vba 调用 .net 库方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
