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:
- 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.)
- Create a new class library project.
- Add a class to the project and give that class the methods that you want the VBA code to be able to execute.
- Use the ComVisible attribute to allow the VBA code to use the class and methods.
- 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.
- Set the project properties.
- Open the project’s property pages.
- 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.)
- On the Build property page, check the “Register for COM Interop” box.
- 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:
- Create the Excel workbook.
- Save the workbook as a macro-enabled workbook (with a .xlsm extension).
- Open the VBA editor.
- While inside the editor, open Tools > References. The DLL should appear in the list. Select it.
- 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.
- 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
- 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.
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!
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,
Sorry but I haven’t tried this for a long time so I don’t know what could be going wrong.
Anyboday has answer for Fabiel Santos? I have same problem with system with Windows 10 and VS2013.
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
Hi.. Did you get the answer… I am facing this problem..
Can you please let me know at vinaymauryably AT gmail.com
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
Thanks Ander!
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
Thanks for the note! That should help a lot of people!
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
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;
….
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?