Make a C# DLL and use it from Excel VBA code

Posted on October 30, 2013 by Rod Stephens


This example shows how to make a C# DLL and then use it from VBA code running in Excel. A similar procedure should work for other Office applications. Note that some of the steps may require modification and significant fiddling around in different versions of Windows. For example, this was easy in Vista but I didn’t quite get it to work in Windows 8. (Post a comment if you get it working in Windows 8.)

Make the DLL

To make the C# DLL:

  1. Run Visual Studio as Administrator. (You need elevated permissions to let Visual Studio register the DLL properly. You can use regasm to do this yourself but it’s easier if Visual Studio does it for you.)
  2. Create a new class library project.
  3. Add a class to the project and give that class the methods that you want the VBA code to be able to execute.
    1. Use the ComVisible attribute to allow the VBA code to use the class and methods.
    2. Use the ClassInterface attribute to create a description that VBA’s IntelliSense can read so it can “see” the methods.

This example uses the following class:

using System.Runtime.InteropServices;
namespace howto_dll_for_excel
{
    [ComVisible(true)]
    [ClassInterface(ClassInterfaceType.AutoDual)]
    public class CSharpTools
    {
        [ComVisible(true)]
        public string AddBrackets(string value)
        {
            return "[" + value + "]";
        }
    }
}

This method takes an input string, surrounds it with brackets, and returns the result.

  1. Set the project properties.
    1. Open the project’s property pages.
    2. On the Application page, click “Assembly Information” and check the “Make assembly COM visible” box. (The VBA code will use COM to communicate with the DLL.)
    3. On the Build property page, check the “Register for COM Interop” box.
  2. Build the project. Visual Studio should create .dll and .tlb files.

Note: A few times while I was doing this, Visual Studio did not create the .tlb file. I have no clue why not. When I unchecked the boxes in the project’s property pages, saved, rechecked the boxes, and rebuilt the program, it built the .tlb file just fine.

Also note that you cannot rebuild the .dll and .tlb file if another program (such as the Excel workbook) is attached to the library. Close the Excel workbook before you rebuild the DLL!

Make the Excel Workbook

To make the Excel Workbook:

  1. Create the Excel workbook.
  2. Save the workbook as a macro-enabled workbook (with a .xlsm extension).
  3. Open the VBA editor.
  4. While inside the editor, open Tools > References. The DLL should appear in the list. Select it.
  5. Now if you open the Object Browser (press F2), you should be able to select the DLL in Library dropdown. Then you can click on the class and see its public members.
  6. Create a VBA macro to use the tool. The DLL’s namespace, class, and method should all appear to IntelliSense.

In this example, I added a button to a worksheet. When you click the button, the following Click event handler executes. (The code that clals the C# DLL is highlighted in blue.)

This example uses the following VBA macro:

Sub Button1_Click()
Dim sheet As Worksheet
Dim tools As howto_dll_for_excel.CSharpTools
Dim value As String
Dim result As String
Set sheet = ActiveSheet
value = sheet.Cells(1, 1)
Set tools = New howto_dll_for_excel.CSharpTools
result = tools.AddBrackets(value)
sheet.Cells(1, 2) = result
End Sub

  1. Run the macro.

It took a lot of fiddling to make this work so I won’t be surprised if you need to change things a bit for your version of Visual Studio and Windows. If you do, please post a comment so others with a similar environment can benefit from your experiences.

   

This entry was posted in Excel, interoperability, Office, VBA. Bookmark the permalink.

13 Responses to Make a C# DLL and use it from Excel VBA code

  1. 212 3484988 says:
    March 4, 2014 at 5:11 am

    Thanks so much for the great article! It gave me the info I couldn’t find anywhere else.

    It would be great to have a sequel on how to deploy the .dll to another machine. I imagine we have to do manually the registration that Visual Studio does automatically in response to the two checkbox settings.

    But thanks again!

    Reply
  2. Fabiel Santos says:
    June 10, 2016 at 9:20 am

    Hi, First of all I wanna thank you for the tutorial, however it didn’t work. I followed all steps but I’am getting an error on VBA code line Set tools = New howto_dll_for_excel.CSharpTools.

    The message is “The ActiveX object cannot be created”.

    So I was wondering, could you evaluate what wend wrong ?

    regards,

    Reply
  3. MMehd says:
    June 23, 2016 at 4:47 pm

    Anyboday has answer for Fabiel Santos? I have same problem with system with Windows 10 and VS2013.

    Reply
    • Fabiel Santos says:
      June 26, 2016 at 11:56 am

      Hi MMehd, if I found an answer I’ll let you know. I’ve seen lot’s of other examples, but none of them so clean and easy as this one. Anyway, let us know if you find a solution ….regards

      Reply
      • Vinay Kumar Maurya says:
        December 7, 2016 at 12:47 pm

        Hi.. Did you get the answer… I am facing this problem..
        Can you please let me know at vinaymauryably AT gmail.com

        Reply
        • Ander says:
          December 9, 2016 at 12:26 pm

          Hello, sorry for my English.
          I discovered the problem. I hope it solves for everyone.

          1) In item 4.C) On the Build property page, uncheck the “Register for COM Interop” box;
          2) Run “regasm name.dll / codebase” (This step is important);
          3) Run regasm name.dll /tlb:name.tlb;
          4) Repeat the other steps according to the tutorial.

          *Perform regasm as Administrator

          Reply
  4. Johnny Ben-Tovim says:
    February 14, 2017 at 11:00 pm

    I found that the error “The ActiveX object cannot be created” was due to the class library being built for “Any CPU” instead of x64 as I am using office 64 bit

    Reply
  5. Günther Spitzer says:
    January 8, 2018 at 5:36 am

    Thank you all very much for posting these infos and sharing your knowledge.
    I was searching for a solution for the same problem for a while … no matter what I tried I always ran into ‘Error 429’ as described above. Even on my old notebook I now received this error message even though I am sure it worked some 2 years ago when I first tried to implement the access from Excel and Visio to a C# DLL.
    For me it helped to uncheck the “Register for COM Interop” box and select x64 for the CPU. Other than that I use the same procedures as described above.

    blue skies
    Günther

    Reply
  6. Eyal Catz says:
    August 12, 2018 at 4:10 am

    I followed the instruction and almost every thing is working, but in my C# class I have a jagged array member (see Tbl below) that I can’t see or access in VBA. All other members are fine but this one.

    I can’t use a two dimensional array because I am serializing the class to xml.

    [ComVisible(true)]
    [ClassInterface(ClassInterfaceType.AutoDual)]
    [Serializable]
    public class DIMTABLE : DB_Class
    {
    public string[] DIM;
    public int X;
    public object[] ValArray;
    public object[][] Tbl;
    ….

    Reply
    • RodStephens says:
      August 13, 2018 at 9:19 am

      I don’t know what might be wrong with that. Have you tried other serializable classes? Could VBA be not understanding that part? Does it understand if you remove the Tbl field?

      Reply

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.