VBA - ActiveX Cant Create Object for .net dll with dependencies

Question

I have written a .Net library which encapsulates some of my business logic that needs to interact with a legacy system. The purpose of this library is to expose a COM interface so that I can call it from Office Applications using VBA.

I've done everything I need to do to get it registered using 64bit Regasm.exe with /codebase /tlb flags. It is visible in my Office Applications and is even showing in intellisense. However, I keep getting the "ActiveX Component cant create object" error.

To try and determine if there was an error in my setup/configuration of the COM interface I created a sepearte .Net library as part of my solution with just a basic class and method to return a "Hellow World". This actually works as expected i.e. I register it in the References dialog in the VBA application and can instantiate it and run the "HelloWorld" method.

Dim simple as SimpleInterface.MyClass
Set simple = New SimpleInterface.MyClass

MsgBox simple.HelloWorld("Say Hello")

Then I make a reference in my new basic VS project to my desired project (business logic) and make a call to one method in here and then make another method in my simple project to expose as a COM interface. I unregister the dll and register as in the method above.

In my VBA application I then unselect the .tlb file close it down re-open it and then re-reference it and then try and run the code. This is when I get the "ActiveX componenent can't create object". I'm assuming this has somethingn to do with dependent dlls in my project but Im unsure as there is very little details with the error.

I'm not sure what I am supposed to do here? Do I need to regsiter all other dependent dlls i.e. I have about 2 or 3 external dll's which I just make a reference to in the VS project? CAn someone tell me how it is possible to find any more futher detail i.e. which is the exact offending dll?

Answer 1

The "ActiveX Component can't create object" error usually means that there was a problem locating the assembly or one of its dependencies, or that the assembly does not contain any public class with the requested ProgID. To get more details for troubleshooting assembly loading, I would suggest using the Assembly Binding Log Viewer.

Some things to check

• So you're actually using 64-bit edition of MS Office? (I've been under the impression that the 32-bit edition is still the most commonly used one, due to incompatibility between the 64-bit and 32-bit editions regarding file formats/limitations for things like Excel documents, but maybe this isn't the case any more...?)
• What target platform have you selected when you compile the Visual Studio project?
• Has the project ever been compiled with the checkbox "Register for COM Interop" checked in the project settings?

What I'm getting at here is this: Is there any possibility that you're actually building/registering for the wrong platform/bitness (i.e., not matching the bitness of your MS Office installation)? Could there be an old registration for the correct bitness, which might explain why you still see the Type Library in the References dialog of the VBA IDE, despite building for the wrong target platform?

One other big thing to keep in mind when exposing .NET classes through COM interop

By default, Visual Studio will generate new random ProgID GUIDs for your exposed classes every time you compile the project. This is why you have to go through the trouble of removing references and then adding them back again in the VBA IDE to make things work again after re-compiling your .NET project.

Not only that, but it also clutters up the registry with lots of obsolete keys unless you make sure to always unregister (RegAsm /u /tlb) your assembly before overwriting it with a newly compiled version.

To prevent these things, you should explicitly set the ProgID GUIDs using ProgID attributes on the COM-exposed classes in your .NET code. It is also recommended to use the ComVisible attribute, and have it set to false on assembly level, and explicitly set to true only on those types that need to be exposed to COM.

Regarding .NET dependencies

You only have to register the DLL that contains the "entry point" to your component, i.e. the method you're calling from VBA. The .NET runtime needs to be able to find the dependencies, though, just like it does for any type of .NET assembly. This is usually achieved by keeping copies of the dependencies in the same folder as the entry point DLL. Visual Studio usually copies dependencies to the output folder by default for assembly/project references, except for assemblies that exist in the GAC.

How to use the Visual Studio debugger

You can debug your .NET code while it is being called from VBA by attaching the Visual Studio debugger to the running excel.exe process (Debug > Attach to Process from the menus). Make sure that "Managed" is mentioned in the Type column for the Excel process, and that that the debugger will attach to that (should happen automatically, unless you've fiddled with the settings).

If "Managed" isn't mentioned for the Excel process, it means that Excel hasn't loaded your assembly yet; try running the VBA code with a breakpoint right after the statement that creates the COM object, then try attaching the debugger again.

After you have attached the Visual Studio debugger, you will have access to a lot more information about what is going on. You will then be able to have the VS debugger break whenever a .NET exception is thrown, and you can also add breakpoints and step through the .NET code. Really worth looking into, if you haven't already.