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.

Answer 2

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. =).

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

Answer 3

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

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#.

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 before you get that many records.

Answer 4

The other answers to this question involve the VBA macro calling an external method. But there is an easier way. Your GUI app can attach to the workbook SheetSelectionChange event or sheet Change event, which will be triggered when any value changes.

Because you probably don't want to slow down everything, I would recommend adding an additional sheet, add named ranges to describe what each value means, then hook the sheet change event to intercept when these values are changed and update your GUI.