How to make .dll available for C# and Excel VBA

4
votes

I have a DLL that I want to use in C# projects as well as Excel VBA. I used regasm.exe to register the DLL and create a TLB . I was now able to reference it in VBA. My problem is, that I would need to my C# apps still can't find the DLL. I would have to add either a copy of the DLL or a .config - file in the folder of each C# app for them to work. I can see the reference in Visual Studio under COM, but get an error when trying to use that reference (something Active-X related, I can look it up if it's important).

So I tried using gacutil.exe (unregistered first with regasm.exe) and registered my DLL in GAC. Now I don't need the copy or .config, my C# apps work again. Excel VBA still needs the registration with regasm.exe though, I can't add a refrence after only using gacutil.exe.

Here my question: How do you register a DLL when you want to use it for C# apps AND Excel VBA? Is it possible to use both regasm and gacutil without having any problems?

2
c#exceldllvba
@Steve Yes, I just don't know if it will cause any problem, having it registered more than once. That's what I am concerned about. This is the first time, I would use both regasm and gacutil. – Pasoe
I don't know why you think the two registrations interact in any way. The COM (regasm) registration writes its stuff in the Registry and it is the cumbersome way required for languages like VBA to find their things, the second involves special folders and a fool proof mechanism to find the correct libraries and it is used by a NET assemblies (or VS references system) – Steve
@Steve well, I don't have any experience with registration of .dlls. I couldn't find any information on interactions between those two, maybe I should have guessed that there is none... Still, thank you for your fast help. – Pasoe
Your only problem should arise when you update your NET library and you need to update the Registry. This is always the main problem with COM based registrations. It is relatively easy to do on a machine under your control, sometime a nightmare on a customer machine with tons of other software installed. (By the way, I prefer to have my NET libs in my app folder and try to implement a fast and secure way to update my apps through internet web services that allow also a certain degree of control on the licensing issues) – Steve

2 Answers

2
votes

After running regasm you can then use gacutil /i to install the assembly into the GAC. Once this is done, add a reference to the assembly to your .NET project using the Browse tab. Once the reference is added, locate the reference in the project and view the properties for that reference. Change "Copy Local" to "False".

At run time your .NET project will look for the assembly locally and, when it cannot find it, will then check the GAC. Assuming you've run the gacutil command it will then locate it there.

2
votes

A somewhat different approach is to incorporate your C# library in an Excel-DNA .xll add-in. This allows you to make the library available to VBA in Excel without registration or requiring admin rights, and the same add-in can include worksheet UDF functions, ribbon customization etc..

A detailed write-up of exposing C# libraries to Excel VBA via Excel-DNA by Mikael Katajamäki can be found here: http://mikejuniperhill.blogspot.com/2014/03/interfacing-c-and-vba-with-exceldna-no.html and http://mikejuniperhill.blogspot.com/2014/03/interfacing-c-and-vba-with-exceldna_16.html.