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

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.

2 Comments

  1. Hello Tom,

    I am looking for a tool which would convert the excel vba codes to a dll. I came to know about your website from an excel forum.

    When I click on the link, it shows Error 404 Nothing Found:
    “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!”

    Could you please look into it and let me know when it is available?

    Thank you & regards,
    Don

    • Hi Don, thanks for pointing this out. Indeed i had a compiler for VBA -> DLL. I regret however unfortunately with the evolution of .NET I had to retire the tool as it no longer worked are required constant maintenance. The additional issue was that many cases you need to adjust the VB code anyway before moving to DLL. The process above can be adjusted to VB.NET instead of C# I am sure – then you could copy most of your code from Excel and do adjustments in case of build fails.

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.