Calling C# Code from VBA
Do you have code written in C# that you want to invoke in Excel VBA? In order to be able to achieve this, you will have to expose your C# code to COM (Component Object Model). COM is a platform-independent, object-oriented system for creating binary software components that can interact. COM objects can be created in a variety of languages and can be invoked from within any programming language that supports COM. We will show you in this post how to create a C# COM-visible class and invoke it within Excel VBA project.
Creating a COM-visible Assembly in C#.NET
We will use Visual Studio 2019, .NET Framework 4.8.1 and Microsoft® Excel® 64-bit in this post. Other versions of Visual Studio should also work with no issues as long as the .NET Framework is set to a version lower than 5.0 as support for COM was suspended in .NET Framework 5.0.
1- Open Visual Studio 2019 as Administrator and click on Create a new project then select C# Class Library (.NET Framework) from the menu. Make sure you select Class Library (.NET Framework) and not Class Library.
2- Right-click on your project in the Solution Explorer, then go to Properties.
3- Under the Application section, change Target Platform to .NET Framework 4.8.1.
4- Click on Assembly Information and check Make assembly COM-visible. This makes all public types in the library COM-visible. We will show below how to hide types that you wouldn’t want to expose to COM.
5- Then go to the Build section, change Platform target to x64 and click on Register for COM interop.
This option allows Visual Studio to register the dll on your machine and generate keys in HKEY_CLASSES_ROOT in the Registry. If this option is left unchecked, you will have to register the dll manually using the following command:
"C:\Windows\Microsoft.NET\Framework64\v4.0.30319\RegAsm.exe" /codebase "…\MyComLib.dll"6- Rename Class1.cs to MyComClass.cs, add following code to your MyComClass.cs file.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Runtime.InteropServices;
namespace MyComLib
{
[ComVisible(true)]
public interface IMyComClass
{
void Add(ref double result, double x, double y);
}
[ComVisible(true)]
public class MyComClass:IMyComClass
{
public void Add(ref double result, double x, double y)
{
result = x + y;
}
}
}
Please note the [ComVisible(true)] attribute before the name of the MyComClass class and IMyComClass interface. It’s the attribute that allows you to expose or hide a certain object to COM. If you have a particular object that you don’t want to expose, simply set this attribute to false.
7- Rebuild the solution. If you encounter the following error, make sure you re-run VS2019 as Administrator
At this point, we have created a COM-visible assembly that Visual Studio automatically registers on the user’s machine. If you look under your bin\debug folder in Windows Explorer, you will see that a .tlb (type library) file got created after Visual Studio registered the assembly on your machine.
Calling a COM-visible class in Excel VBA
1- Open Excel and create a new blank workbook, then go to Developer>Visual Basic
2- In the Visual Basic editor go to Tools->References then browse to the Debug folder where .tlb file is and add it.
3- Add a module to your VBA project and add the following code
Sub Test()
Dim MyComLibObj As New MyComLib.MyComClass
Dim result As Double
MyComLibObj.Add result, 1#, 2#
Debug.Print result
End Sub
In order to be able to use our COM-visible class in Excel VBA, we have to create an instance of our MyComClass as shown at line 2 in the code above. Please note that MyComlib is the name the tlb/dll file.
Here’s the output from our method MyComClass.Add in the immediate Window:
