Calling C# Code from Excel 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 the 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. We will be using VS2019, .NET Framework 4.8.1 and Microsoft® Excel® 64-bit.
Creating a COM-visible Assembly in C#.NET
Open VS2019 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.
Right-click on your project in the Solution Explorer, then go to Properties.
Under the Application section, change Target Platform to .NET Framework 4.8.1.
Click on Assembly Information and check Make assembly COM-visible.
Then go to the Build section, change Platform target to x64 and click on Register for COM interop.
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.
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
Open Excel and create a new blank workbook, then go to Developer>Visual Basic
In the Visual Basic editor go to Tools->References then browse to the Debug folder where .tlb file is and add it.
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 MyComLibObj.Add in the immediate Window:
