Communicating between C# and VBA

Question

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.

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.

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.

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-bar in my GUI app.

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.

Answer 1

Creating a C# COM-visible class is pretty easy, and (as you said in your comments) it is fun. Here's a small sample.

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

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.

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\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:

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.

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.