I have made a GUI in C#. Now I want to create an API to use in vba to ease automation and make use of the team’s existing vba code. This guide gives a basic example of how to do that. Examples I found online were overly complicated and/or not so useful. This short guide should get you started in calling your C# .NET functions from Excel VBA. If you plan on using a GUI, see the other guide as this approach will run into problems. Please let me know if you have trouble.

Update: As it turns out, this does not effortlessly lead into a COM accessible GUI as I originally suspected. This documentation below works great until you try to add in the actual GUI application. For the GUI application, see this guide: How to tie a C# .NET GUI to a callable C# .NET class library dll from Excel VBA: Guided Minimum Framework Example

After making different “HelloWorldGUI via VBA/C#” style minimal working solution examples, I picked Named Pipes for now because it was the easiest solution I found (outside of COM exposed DLL solution below for non-gui applications. .NET 3.5+ required

Possible solutions included:

Run the GUI from the VBA – No, I want a standalone GUI that can be attached to
Use AutoItX3.dll to literally click the buttons and type using virtual keyboard and mouse – No, doable but overly difficult and complicated
Use .NET’s UI automation to control UI elements found through Windows SDK inspect tool – Pretty good, but since we have the GUI source code, there should be a more direct solution\
Use UDP port on local machine to link programs
Communicate through set memory location on computer – too low level. Something should already exist that does this safely without problems
Use WCF (Windows Communication Foundation) Named Pipes to communicate between VBA/C# and WPF (Windows Presentation Foundation) – Looks the cleanest and easiest way

Step-By-Step Guided Example

Step-By-Step Guided Example
1. Create C# Library Class
2. Make Library Class Visible as COM Library
3. Call Public Functions from Excel VBA
4. Deployment
- Unblock the DLL (Windows Security)
- Registering the Assembly
- Related articles
References

1. Create C# Library Class

Put this code in a new C# file in a new Visual Studio project. It is bare bones with one simple function that will later be called from Excel vba.

I named the project and solution both ZZTEST_EXCEL_LIB for easy finding in the excel references list.

Sample Code Collapse source

2. Make Library Class Visible as COM Library

In the project properties, change the output type to Class Library

In Assembly Information, check “make assembly COM-Visible”

In the project properties under the build tab, check “Register for COM interop”

3. Call Public Functions from Excel VBA

In Excel, enable the developer tab and launch visual basic (google this step if unclear)

From visual basic, select tools and then references. Select the desired library, in this case “ZZTEST_EXCEL_LIB”

Run the VBA code

Collapse source

Voila

4. Deployment

This is great, but it will only work on your computer. You must follow these steps to register the DLL on another computer. TK I am looking into building an installer to do this

Everything in the C:\Program Files (x86)\Maxim\GUI_BRIDGE path must be placed in this exact path on the target computer.

Unblock the DLL (Windows Security)

Registering the Assembly

Start the command prompt as administrator

In the terminal window, run regasm with /codebase option on the dll.

“C:\Windows\Microsoft.NET\Framework\v4.0.30319\RegAsm.exe” /codebase “C:\Program Files (x86)\Maxim\GUI_BRIDGE2\gui_bridge2_project\gui_bridge2_project\bin\Debug\ZZ_GUI_BRIDGE2_PROJECT.dll”

Regasm will warn about RA0000 ~ please give your assembly a strong name and re-register it. Types registered successfully.