Communication 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 information is subsequently processed using a relatively complex VBA module that collects all the information, applies formulas, sets ranges and generates graphs, etc.

However, there are two problems: I am an amateur programmer, so my VBA procedure is quite inefficient. This is not a huge problem for me (I just get up and drink coffee during her work), but for other users it can be a hassle, because then I don’t know why it takes so long. I want a graphical representation of progress. Application configuration is done through a text file. I want to provide a decent graphical interface for configuring.

To achieve this, I am looking for a way for my C # WinForms application to communicate with my VBA application. I know how to run a VBA routine from my C # application, but I don't know how I can let them comment in real time.

Here are two things I specifically want to achieve:

• I already have a log file that is saved at the end of the VBA procedure. Instead, however , I want to send log / debug messages to my C # application in real time (not only at the end of the application), so messages can be displayed in my graphical application, as they are created by the VBA application.
• I also want the VBA application to send real-time progress information to my GUI, so I can create a graphical progress bar in my GUI.

I already thought about communicating through standard output. I know how to read using standard output using C # /. Net, but I'm not sure how to write to a StdOut stream using VBA.

I’m sure that many will indicate that what I'm trying to achieve is stupid and old-fashioned (or completely unnecessary), but as an amateur programmer, it seemed to me that this is really a complicated and funny project for me that can teach me something .

Answer 1

Creating a C # COM visible class is quite simple, and (as you said in your comments) is fun. Here is 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 enter your project properties, on the "Signing" tab, check the box to sign your assembly, and create a new "strong name key file". This will help prevent version problems in your registered DLL.

Compile it and register the DLL with regasm at the Visual Studio command prompt. You will use 32 or 64-bit regasm depending on which version of Office you are using ... you will find RegAsm in C:\windows\Microsoft.NET\Framework or C:\windows\Microsoft.NET\Framework64 (respectively 32 and 64 bit):

C:\windows\Microsoft.NET\Framework\v4.0.30319\RegAsm.exe /codebase /tlb CSharpCom.dll

This will register your Windows DLL, so now in the VBA editor you can go to Tools-> References and find your COM namespace "CSharpCom". Check this box and now you can 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 VBA calls a specific method; you must use this to create a progress bar form. However, it is worth considering that VBA is single-threaded. This means that everything else freezes while your code is running, so everything can get a little confused.

At the top of my head, you can create 3 methods in the COM visibility class: one to open the form, one to update the progress (calling the VBA DoEvents () method right after your VBA calls the update () method of your COM object to let Office handle screen updates), and one to close it. You must call Dispose () on the form; what can be done in the close method, but I think it could potentially cause a memory leak / problem if your VBA crashes and your close method is never called - just something else to consider.

Answer 2

You can use the .NET DLL with the VB6 project and instantiate any object that you defined in your assembly. This is done by registering a .NET assembly (DLL) for COM interoperability (used in VB6) by creating a type library file (.tlb). The .tlb file contains additional information so that your VB6 project can use your .dll. Please note: your VB6 project will not reference the .dll, but the corresponding .tlb file. You can use the Regasm.exe utility to create and register a type library and register the location of a managed assembly. Then it's just a matter of instantiating your .NET object, whether it be 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 that the code in this link does roughly what you want:

• C # code creates a COM object (in the example it’s IE, but from your description you already managed to create an instance of your VBA procedure)

• It attaches the .Net event handler (using + =) to the event generated by the COM object (when the name changes)

• It defines an example of processing processing code.

http://msdn.microsoft.com/en-us/library/66ahbe6y.aspx

I do not claim to understand this code, my goal is to show that this solution would be confusing! You have to build it in only one solution. In general, the VBA solution will evolve faster (you only need to learn the forms of VBA, which are lightweight), but old technologies. A general C # solution will evolve more slowly, but then you will learn about C #.

Performance is actually a problem. If it is now inefficient, what happens when you have 5x so many records to process? You must solve the performance problem before you get a lot of records.