vba C#和VBA之间的通信
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13436028/
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
Communicating between C# and VBA
提问by romatthe
At the request of my boss I created a small set of scripts that are used to periodically monitor the status of certain devices and processes. This info is subsequently processed using a relatively elaborate VBA module that gathers all info, applies formulas, sets up ranges and generates graphs, etc.
应老板的要求，我创建了一小组脚本，用于定期监控某些设备和进程的状态。随后使用相对复杂的 VBA 模块处理此信息，该模块收集所有信息、应用公式、设置范围并生成图形等。
There are two problems however: I'm an amateur programmer, so my VBA routine is quite inefficient. That's not a huge problem for me (I just get up and get a coffee while it's running), but for other users this can be a hassle, as then don't know why it's taking so long. I want a graphical representation of progress. The configuration of the application is done through a text file. I want to provide a decent GUI for setting up the configuration.
但是有两个问题：我是一个业余程序员，所以我的 VBA 程序效率很低。这对我来说不是一个大问题（我只是在它运行时起床喝杯咖啡），但对于其他用户来说这可能很麻烦，因为那时不知道为什么要花这么长时间。我想要进度的图形表示。应用程序的配置是通过文本文件完成的。我想提供一个不错的 GUI 来设置配置。
In order to achieve this, I'm looking for a way to let my C# WinForms application communicate with my VBA application. I know how to run a VBA routine from my C# app, but I don't know how I can let them comminicate in real-time.
为了实现这一点，我正在寻找一种方法让我的 C# WinForms 应用程序与我的 VBA 应用程序进行通信。我知道如何从我的 C# 应用程序运行 VBA 例程，但我不知道如何让它们实时通信。
Here are the two things I specifically want to achieve:
以下是我特别想要实现的两件事：
- I already have a log file that's saved at the end of the VBA routine. Instead of that however I want to send the log/debugging-messages to my C# application in real-time(not just at the end of the application) so the messages can be displayed in my GUI app as they are generated by the VBA app.
- I also want the VBA app to send info about real-time progress to my GUI app so I can create a graphical progress-barin my GUI app.
- 我已经有一个保存在 VBA 例程末尾的日志文件。然而，我想将日志/调试消息实时发送到我的 C# 应用程序（不仅仅是在应用程序结束时），以便消息可以显示在我的 GUI 应用程序中，因为它们是由 VBA 应用程序生成的.
- 我还希望 VBA 应用程序向我的 GUI 应用程序发送有关实时进度的信息，以便我可以在我的 GUI 应用程序中创建图形进度条。
I've already thought about communicating through the Standard Output. I know how to read from the Standard Output using C#/.Net, but I'm not sure how I would write to the StdOut stream using VBA.
我已经考虑过通过标准输出进行通信。我知道如何使用 C#/.Net 从标准输出中读取数据，但我不确定如何使用 VBA 写入 StdOut 流。
I'm sure many would point out that what I'm trying to achieve is stupid and old-fashioned (or totally unnecessary), but as an amateur programmer it seemed like a really challenging and fun project for me that could teach me a thing or two.
我相信很多人会指出我想要实现的目标是愚蠢和过时的（或完全没有必要），但作为一个业余程序员，这对我来说似乎是一个非常具有挑战性和有趣的项目，可以教会我一些东西或两个。
回答by transistor1
Creating a C# COM-visible class is pretty easy, and (as you said in your comments) it is fun. Here's a small sample.
创建 C# COM 可见类非常简单，而且（正如您在评论中所说）很有趣。这是一个小样本。
In a new C# Library project, add:
在新的 C# 库项目中，添加：
using System;
using System.Runtime.InteropServices;
using System.Windows.Forms;
namespace CSharpCom
{
[ComVisible(true)]
[InterfaceType(ComInterfaceType.InterfaceIsIDispatch)]
//The 3 GUIDs in this file need to be unique for your COM object.
//Generate new ones using Tools->Create GUID in VS2010
[Guid("18C66A75-5CA4-4555-991D-7115DB857F7A")]
public interface ICSharpCom
{
string Format(string FormatString, [Optional]object arg0, [Optional]object arg1, [Optional]object arg2, [Optional]object arg3);
void ShowMessageBox(string SomeText);
}
//TODO: Change me!
[Guid("5D338F6F-A028-41CA-9054-18752D14B1BB")] //Change this
[InterfaceType(ComInterfaceType.InterfaceIsIDispatch)]
interface ICSharpComEvents
{
//Add event definitions here. Add [DispId(1..n)] attributes
//before each event declaration.
}
[ComVisible(true)]
[ClassInterface(ClassInterfaceType.None)]
[ComSourceInterfaces(typeof(ICSharpComEvents))]
//TODO: Change me!
[Guid("C17C5EAD-AA14-464E-AD32-E9521AC17134")]
public sealed class CSharpCom : ICSharpCom
{
public string Format(string FormatString, [Optional]object arg0, [Optional]object arg1, [Optional]object arg2, [Optional]object arg3)
{
return string.Format(FormatString, arg0, arg1, arg2, arg3);
}
public void ShowMessageBox(string SomeText)
{
MessageBox.Show(SomeText);
}
}
}
You will want to go into your project properties, to the "Signing" tab, check the box to sign your assembly, and create a new "strong name key file". This will help to prevent versioning issues with your registered DLL.
您将需要进入您的项目属性，到“签名”选项卡，选中复选框以对您的程序集进行签名，然后创建一个新的“强名称密钥文件”。这将有助于防止您注册的 DLL 出现版本控制问题。
Compile it, and register the DLL using regasmin a Visual Studio command prompt. You will use either 32 or 64-bit regasm depending on what version of Office you are using... you will find RegAsm in C:\windows\Microsoft.NET\Frameworkor C:\windows\Microsoft.NET\Framework64(32 and 64-bit, respectively):
编译它，并regasm在 Visual Studio 命令提示符中使用注册 DLL 。您将使用 32 位或 64 位 regasm，具体取决于您使用的 Office 版本……您会在C:\windows\Microsoft.NET\Framework或C:\windows\Microsoft.NET\Framework64（分别为 32 位和 64 位）中找到 RegAsm ：
C:\windows\Microsoft.NET\Framework\v4.0.30319\RegAsm.exe /codebase /tlb CSharpCom.dll
C:\windows\Microsoft.NET\Framework\v4.0.30319\RegAsm.exe /codebase /tlb CSharpCom.dll
This will register your DLL with Windows, so now in the VBA editor, you should be able to go to Tools->References and find your COM's namespace "CSharpCom". Check that box, and now you should be able to create your COM objects in VBA:
这将在 Windows 中注册您的 DLL，所以现在在 VBA 编辑器中，您应该能够转到工具->引用并找到您的 COM 的命名空间“CSharpCom”。选中该框，现在您应该能够在 VBA 中创建 COM 对象：
Sub TestCom()
Dim c As CSharpCom.CSharpCom
Set c = New CSharpCom.CSharpCom
c.ShowMessageBox c.Format("{0} {1}!", "Hello", "World")
End Sub
You can add forms to your COM object, and open them when the VBA calls a particular method; you should be able to use this to create a form with a progress bar. One thing to consider, though, is that VBA is single-threaded. This means that everything else gets frozen while your code is running, so things might get a little tricky.
您可以向 COM 对象添加表单，并在 VBA 调用特定方法时打开它们；您应该能够使用它来创建带有进度条的表单。但是，需要考虑的一件事是 VBA 是单线程的。这意味着在您的代码运行时其他所有内容都会被冻结，因此事情可能会变得有点棘手。
Off the top of my head, you could create 3 methods in your COM-visible class: one to open the form, one to update progress (call VBA's DoEvents() method right after your VBA calls the update() method on your COM object, to allow Office to process screen updates), and one to close it. You should call Dispose() on the form; which could be done in the "close" method, but I think it could potentially cause memory leaks/problems if your VBA crashes, and your close method is never called -- just something else to consider.
在我的脑海里，你可以在你的 COM 可见类中创建 3 个方法：一个打开表单，一个更新进度（在 VBA 调用 COM 对象上的 update() 方法后立即调用 VBA 的 DoEvents() 方法，允许 Office 处理屏幕更新），然后关闭它。您应该在表单上调用 Dispose()；这可以在“close”方法中完成，但我认为如果你的 VBA 崩溃，它可能会导致内存泄漏/问题，并且你的 close 方法从未被调用——只是需要考虑的其他事情。
回答by Fredrik
You can use a .NET DLL with your VB6 project and create an instance of any object you have defined in your assembly. This is done by registering the .NET assembly (.dll) for COM interop (use in VB6) by creating a type library file (.tlb). The .tlb file contain extra information so that your VB6 project can use your .dll. Note, your VB6 project will not reference the .dll but the corresponding .tlb file. You can use the Regasm.exe utility to generate and register a type library and register the location of the managed assembly. Then its just a matter of creating an instance of your .NET object, whether it be a WinForm or some other cool class. =).
您可以在 VB6 项目中使用 .NET DLL，并创建您在程序集中定义的任何对象的实例。这是通过创建类型库文件 (.tlb) 为 COM 互操作（在 VB6 中使用）注册 .NET 程序集 (.dll) 来完成的。.tlb 文件包含额外信息，以便您的 VB6 项目可以使用您的 .dll。请注意，您的 VB6 项目将不会引用 .dll 而是相应的 .tlb 文件。您可以使用 Regasm.exe 实用程序生成和注册类型库并注册托管程序集的位置。然后它只是创建一个 .NET 对象的实例的问题，无论它是 WinForm 还是其他一些很酷的类。=)。
Dim MyDotNetObject As MyDotNetClass
Set MyDotNetObject = New MyDotNetClass
MyDotNetObject.SomeMethod(value1, value2)
''end of execution
Set MyDotNetObject = Nothing
See: http://support.microsoft.com/default.aspx?scid=kb;en-us;817248
请参阅：http: //support.microsoft.com/default.aspx?scid=kb;en-us;817248
回答by Nick.McDermaid
I believe the code in this link does roughly what you want:
我相信此链接中的代码大致可以满足您的需求：
C# code creates a COM object (in the example it is IE, but from your description, you have already managed to create an instance of your VBA routine)
It attaches a .Net event handler (using +=) to an event raised by the COM object (when the title changes)
It defines sample event handling code
C# 代码创建一个 COM 对象（在示例中它是 IE，但根据您的描述，您已经设法创建了 VBA 例程的实例）
它将 .Net 事件处理程序（使用 +=）附加到由 COM 对象引发的事件（当标题更改时）
它定义了示例事件处理代码
http://msdn.microsoft.com/en-us/library/66ahbe6y.aspx
http://msdn.microsoft.com/en-us/library/66ahbe6y.aspx
I don't profess to understand this code, my objective is to show convoluted this solution would be! You should really build this in just one solution. A total VBA will solution will be quicker to develop (you only need to learn VBA forms which is easy), but old technology. A total C# solution will be slower to develop but then you will get to learn C#.
我不自称理解这段代码，我的目标是展示这个解决方案的复杂性！你真的应该只用一个解决方案来构建它。一个完整的 VBA will 解决方案将更快地开发（您只需要学习 VBA 表单，这很容易），但技术陈旧。一个完整的 C# 解决方案的开发速度会较慢，但随后您将开始学习 C#。
Performance actually is an issue. If it performs inefficiently now, what happens when you have 5x as many records to process? You should solve the performance issue beforeyou get that many records.
性能实际上是一个问题。如果它现在执行效率低下，那么当您要处理的记录数量是原来的 5 倍时会发生什么？您应该在获得那么多记录之前解决性能问题。
