目前我的老板要求我创建了一个小套，用于定期监测某些设备和工艺的状态脚本。这个信息是用一个相对复杂的VBA模块，用于收集所有信息，适用公式随后处理，设置范围和生成图表，等等。

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程序是非常低效的。这不是对我来说是很大的问题（我刚起床，喝杯咖啡，而它的运行），但对于其他用户来说，这可能是一个麻烦，因为后来不知道为什么要花这么长。我想进步的图形重新presentation。 该应用程序的配置是通过一个文本文件来完成。我想提供一个体面的图形用户界面设置配置。

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应用程序我的C＃的WinForms应用程序进行通信。我知道如何从我的C＃应用​​程序运行VBA程序，但我不知道我怎么可以让他们实时comminicate。

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.

这里有两件事我特别想实现：

我已经有真实保存在VBA程序结束的日志文件。 而不是说不过的我想日志/调试，邮件发送到 我的C＃应用​​程序的实时（而不仅仅是在的结束 应用），这样的消息可以显示在我的GUI的应用程序，因为它们 由应用程序的VBA生成。 我也想VBA的应用程序，以发送信息 对于实时进度，我的GUI应用程序这样我就可以创建一个图形 进度条在我的GUI应用程序。 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-bar in my GUI app.

我已经想到了通过标准输出通信。我知道如何从标准输出使用C＃/。NET读，但我不知道我怎么会写使用VBA stdout流。

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.

我敢肯定，很多人会指出，我想要做到的，是愚蠢和老式（或完全没有必要），但作为一个业余程序员这似乎是一个非常具有挑战性和乐趣的项目，我说可以教我一两件事。

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.

推荐答案

创建一个C＃COM可见类是pretty的简单，（因为你在你的评论说的）很是热闹。下面是一个小样本。

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＃库项目，添加：

In a new C# Library project, add:

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

您将要进入你的项目属性，以签名选项卡，选中复选框，程序集进行签名，并创建一个新的强名称密钥文件。这将有助于prevent版本问题，您注册的DLL。

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.

编译它，并注册在Visual Studio命令提示符下使用 regasm 的DLL。您将使用32位或64位regasm取决于什么版本的Office使用的是......你会在ç找到RegAsm：\ WINDOWS \ Microsoft.NET \框架或 C：\ WINDOWS \ Microsoft.NET \ Framework64 （32位和64位，分别为）：

Compile it, and register the DLL using regasm in 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\Framework or C:\windows\Microsoft.NET\Framework64 (32 and 64-bit, respectively):

C：\ WINDOWS \ Microsoft.NET \框架\ v4.0.30319 \ RegAsm.exe / codeBase的/ TLB CSharpCom.dll

这将注册您的DLL与Windows，所以现在在VBA编辑器，你应该可以去工具 - >参考，并找到自己的COM的命名空间CSharpCom。检查框，现在你应该可以在VBA中创建您的COM对象：

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:

Sub TestCom()
    Dim c As CSharpCom.CSharpCom
    Set c = New CSharpCom.CSharpCom
    c.ShowMessageBox c.Format("{0} {1}!", "Hello", "World")
End Sub

您可以添加表格到您的COM对象，并打开它们时，VBA调用特定的方法;你应该能够使用这一个进度条创建一个表单。有一点需要注意，虽然是VBA是单线程的。这意味着，一切被冻结，而你的code上，这样的事情可能会变得有点棘手。

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.

关闭我的头顶，您可以创建3种方法在COM可见类：一个是打开表单，一个更新的进展（调用VBA的的DoEvents（）方法后，就在您的VBA调用update（）方法你的COM对象，以使Office处理屏幕更新），以及一个将其关闭。您应该调用Dispose（）的形式;这可能向关的方法来实现，但我认为这有可能导致内存泄漏/问题，如果您的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.