Today’s topic concerns using C# from within VBA code(Excel C#). Ever had a C#.NET library you wanted to use in Excel VBA (Excel C#)? Well, as a matter of fact it is possible using the Visual Studio Nuget Unmanaged Exports package.

Now why on earth would you want to use C# in Excel by using dll libaries? Well there are a bunch of reasons to consider. Top of mind:

• Utilizing many available existing C#.NET libraries in Excel VBA
• Multi-threading – Excel VBA is limited to 1 thread whereas C#.NET can leverage multithreading
• Use drivers and connections not available Excel VBA (connect to NoSQL databases and custom systems)
• Protect your code and algorithms compiling them to dll libraries – VBA project protection is not a safe method of protecting your code!
• Ease of creating and managing complicated solutions with the support of VS tools (debugging, performance analysis, testing)
• Portability – make sure you algorithms runs exactly the same in VBA, your C#.NET project and your ASP.NET application

This tutorial will show you how to create a COM-visible DLL in C#.NET in just 5 easy steps:

Create New Visual Studio Project

• Create a new VS project
• Select Class library from the C# group
• Go to Project Properties and make your library to be COM-visible

Show Package Manager Console

To install the Unmanaged Exports template you will need to use the Nuget Package Manager Console. Enable it as shown below:

Install the Unmanaged Exports template to the project

• Open the Package Manager Console
• Paste Install-Package UnmanagedExports into the console and hit enter

Create a class with some example code

• Create a sample “Sample.cs” class
• Insert the code below to the “Sample.cs” file
• Build the Project

using RGiesecke.DllExport;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.InteropServices;
using System.Text;
using System.Threading.Tasks;

namespace TestLib
{
    [ComVisible(true), ClassInterface(ClassInterfaceType.AutoDual)]
    public class TestClass
    {
        public string Text
        {
            [return: MarshalAs(UnmanagedType.BStr)]
            get;
            [param: MarshalAs(UnmanagedType.BStr)]
            set;
        }

        public int Numbers
        {
            [return: MarshalAs(UnmanagedType.SysInt)]
            get;
            [param: MarshalAs(UnmanagedType.SysInt)]
            set;
        }

        [return: MarshalAs(UnmanagedType.SysInt)]
        public int GetRandomNumber()
        {
            Random x = new Random();
            return x.Next(100);
        }
    }

    static class UnmanagedExports
    {
        [DllExport]
        [return: MarshalAs(UnmanagedType.IDispatch)]
        static Object CreateTestClass()
        {
            return new TestClass();
        }
    }
}

Use the C# class in Excel VBA (Excel C#)

Just insert the following code into your Excel VBA project and you are good!

Declare Function CreateTestClass Lib "C:\[Path to the DLL]\TestLib.dll" () As Object

Sub TestTheTestClass()
  Dim testClass As Object
  Set testClass = CreateTestClass()'Creates an instance of TestClass
  Debug.Print testClass.GetRandomNumber'Executes the method
  testClass.text = "Some text"'Set the value of the Text property
  testClass.Number = 23'Set the value of the Number property
  Debug.Print testClass.text
  Debug.Print testClass.Number
End Sub

Notice that C# types have to be converted to VBA types using the MarshalAs tag. Read more about the different Umanaged Types in C# here: Link.
Below is a list of some typical types you might want to leverage:

Excel C# troubleshooting

In case you are experiencing issues with interfacing with the DLL please check the following:

• Did you mark the DLL to be COM-visible in your Project Properties in Visual Studio
• Have you switched the Platform Target in the class library properties to “x86”
• Is the filename of the DLL in your VBA code correct
• Are there any typos in e.g. the class name, methods etc.