c# excel

Using C# in Excel VBA

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

Before you follow this tutorial be sure to check-out my VBA Compiler Add-In for Excel which allows you to easily export and compile your VBA Functions to VB.NET DLL!

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
New Class Library project

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:
Show Package Manager Console

Install the Unmanaged Exports template to the project

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

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:

C# Type Unmanaged Type VBA Type Comment
String UnmanagedType.BStr String
int UnmanagedType.SysInt Integer
bool UnmanagedType.VariantBool Boolean
Class UnmanagedType.IDispatch Object Return a class object to VBA
Array e.g. int[] ar UnmanagedType.SafeArray Array e.g. Dim ar() As Long If you are using an array as an argument be sure to use the C# “ref” get by reference option e.g. ref int[] ar

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.