EXCEL VBA

Calling C# Code from VBA

Introduction

Do you have code written in C# that you want to invoke in 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 an 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.

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;
        }
    }
}

The [ComVisible(true)] attribute before the name of the MyComClass class and IMyComClass interface 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 a, b, result As Double

a = CDbl(Range("B3"))
b = CDbl(Range("B4"))

MyComLibObj.Add result, a, b

Range("B5") = 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 spreadsheet:

Conclusion

We have shown in this post how to create a COM-visible assembly in C# and invoke it in Excel VBA. Please note that such an assembly can be invoked in any Microsoft Product that leverages VBA namely Microsoft Word, Excel, PowerPoint, Access, Publisher, Visio, and Outlook.

Mill Creek Systems, Inc provides software development services in this area. If you have any software needs, please reach out to us by clicking on the “Contact us” button below.