So you have designed a class in Visual Basic (I’m using Visual Studio 2013 on a Windows 7 64-bit machine) and you want to use it in Excel. Here’s how to do it.

Build Your Solution in VB

I assume that your program compiles without any errors. Then the first thing you need to do is use the Visual Studio 2013 to Build your solution. Start by opening Visual Studio in Administrator mode.

Then before you do build there are a couple of things to set up.

1. Right-click on your solution in the Solution Explorer and click on Properties. A new Properties window will pop up.
2. Under “Application” click on the “Assembly Information” button. A new window will pop up.
3. Enable by ticking the “Make Assembly COM-visible”. This specifies whether types in the assembly will be available to the COM (Component Object Model) which essentially enables inter-process communication – in our case enables communication between the VB language and the VBA language.
4. Click OK and go to the “Compile” tab. Here you must ensure your Build Path is correct. To be sure, click the Browse button. You will find your Solution Name as a folder. Double-click on the folder. Find or create the “bin” folder. Then open it. Find or create a Release folder. Then open it and Select Folder on the Release Folder. Or you can simply just type in “bin\Release\” in the Build Output path text box.
5. Enable by ticking the “Register for COM interop”.
6. Save your Project.

Now go to the ribbon and select BUILD > Clean “Solution Name” and then go to BUILD > Build “Solution Name”. This will force the compiler to clean the application folder and re-populate it with the following essential files under C:\…\Visual Studio 2013\Projects\[Your Solution Name]\bin\Release:
1. A dll file.
2. A tlb file.
You need both these files to register your solution. If they do not both exist then check your Build path and try cleaning/building again.

Registering your DLL

It would be easy now just to copy your dll to the System folder and start using it. However if you do this you will find you will not be able to select it when trying to import it or setting a reference to it in VBA. This is because it is not yet registered with Windows.

To register it, first copy the dll and the tlb files to your C:\Windows\SysWOW64\ folder (or use System32 if you are using a 32-bit OS. I am using Windows 7 64-bit so I had to copy it to “SysWOW64”). I initially copied my dll files to the System32 folder but the 64-bit Microsoft .NET Register exe couldn’t find it as it was expecting them to be in the SysWOW64 folder.

Now open Command Prompt in Administrator mode. Note, all this work must be done in Administrator mode. You now need to go to the .NET Framework folder by typing in your equivalent of “cd C:\Windows\Microsoft.NET\Framework\v4.0.30319”. Note you will need to use whichever framework is currently being used by your Visual Studio 2013 compiler. Mine was v4.0.30319 at the time of writing. Once in this folder run the “RegAsm.exe” by typing in the following command: “RegAsm.exe C:\Windows\SysWOW64\[Solution Name].dll /codebase”. Press Enter.

You should get the following message: “Microsoft .NET Framework Assembly Registration Utility version 4.0.30319.18408 for Microsoft .NET Framework version 4.0.30319.18408 Copyright (C) Microsoft Corporation. All rights reserved. Types registered successfully.

Troubleshooting this Registration Step

This step didn’t work for me first go because of the following issues:
1. I was not using an Administrator Command Prompt.
2. I was in the wrong .NET framework folder running the wrong RegAsm.exe application. Had to check which .NET framework my Visual Studio 2013 was using.
3. My dll and tlb files were still in the System32 folder and not in the SysWOW64 folder. RegAsm.exe in .NET framework was expecting the files to be in the 64-bit system folder.
4. (see below) I ran the registration without the “/codebase” option which caused problems when I tried to use the code in VBA.

Using your DLL

Now open Microsoft Excel. Hit Alt-F11 and go in to VBA mode. Create a new module. Go to Ribbon > Tools > References and click the “Browse” button. Browse to your newly registered dll file in the C:\Windows\SysWOW64\[Solution Name].dll. Click Open.

Go back to the References window and scroll down to find your [Solution Name] reference. Enable it by checking the tick-box. Click OK and go back to your Module.

No Intellisense!

When I first did this I was disappointed to find that upon checking the VBA Object Explorer that my dll had no members! Even though my VB solution clearly had well-defined, public member functions I could not see my public methods in the Object Browser – although the dot operator still worked but without intellisense. Apparently this had something to do with the fact that I registered the dll without the “/codebase” option. So I re-registered by solution including the codebase option, it registered (with a warning this time) but still without intellisense.

Then I read that I actually needed to build an Interface which my class implements. So I did, rebuilt it, re-registered it, and still no intellisense!

The solution was to first use the System Runtime Interoperability Service by importing it in to the preamble of my class like so:

Imports System.Runtime.InteropServices

Then, put <ClassInterface(ClassInterfaceType.AutoDual)> in front of my Public Class declaration like so:

 <ClassInterface(ClassInterfaceType.None)> Public Class [Solution Name]
Implements I[Solution Name]

Note that now my class had a well-defined interface due to the Implements.

Then I performed a clean and a re-build. Re-registered the dll again. And re-imported the dll in to my Excel workbook. Suddenly all my functions worked with intellisense.

Some Remarks About RegAsm.exe Codebase

I must warn you that Microsoft discourages people from using the /codebase technique. They say that it was designed as a development process and that it should not be used in production applications. I’m not sure I fully understand their rationale. You should probably look into the documentation for REGASM and other references in MSDN and make up your own mind about this.

Some Remarks About the System Runtime Interoperability

The difficulty in getting IntelliSense working is that, by default, COM type libraries generated by .NET expose pure dispinterfaces (they implement only

IDispatch

, and the type library won’t even list the dispinterface members). In VB6 terminology, your classes are exposed as objects, and all method names and parameters must be determined at run-time. The generation of the COM class is controlled by an attribute applied to your class, called

ClassInterfaceAttribute

Here are the options:

Auto Dispatch (Default – No IntelliSense)

ClassInterfaceType.AutoDispatch

This is the default and so it applies currently to your class. Your class is exposed as a pure dispinterface, and can only be late-bound. No IntelliSense. The TLB doesn’t even list the dispinterface members, so a late-bound client cannot ever cache any details about what your class exposes.
This allows maximum flexibility to add, change and remove members with impunity, at least insofar as not hard-crashing the client is concerned. I call this “scripting mode”.

AutoDual (Easy but Risky – You Get IntelliSense but it is Awkward)

ClassInterfaceType.AutoDual

This produces a COM interface automatically for you, one that includes all the details of the methods you are exposing. That means you get IntelliSense and you don’t have to worry about creating an explicit interface. However, versioning is a royal pain. You have to be very careful to stop all clients before recompiling and/or re-registering your object, or you will be in a world of hurt if your method signatures changed in any way. I call this “VB6 mode”, because to me it looks a lot like what VB6 did for you (COM versioning on VB6 is also a royal pain).
Microsoft also strongly discourages people from using AutoDual, I suppose because changes to the generated interface can happen more easily without you noticing. I haven’t actually had a chance to use it yet, but I’m not sure that it is that much more dangerous than None.

None (Hardest but Awesome – You Get Perfect IntelliSense)

ClassInterfaceType.None

This is the strictest mode, that asks you to expose your objects more explicitly, kind of like you would have done in C++. You must declare one or more interfaces with the methods that you want to expose, and make the class explicitly inherit from the interface(s). If you inherit from more than one interface, the first one will be picked as the [default] interface, but you should probably designate one explicitly via the 

ComDefaultInterfaceAttribute

. If you don’t inherit from any interface, your Class will inherit straight from 

IDispatch

. (All .NET classes exposed as COM objects are exposed via IDispatch).

The (None) option is my preferred mode, but that might be because I began with OOP in C++. You get IntelliSense, but you must design, declare and inherit explicitly from an Interface. Obviously, you only get to call the members listed in the Interface.

Summary

In summary: If you want to get IntelliSense, you need to apply to your class either

ClassInterface(ClassInterfaceType.None)

(and put your methods in an interface that you explicitly implement), or

ClassInterface(ClassInterfaceType.AutoDual)

Either way, you have to be very careful to stop your clients (and maybe even “removing” and “re-adding” the references) before making changes to your assembly.