Use COM object from Excel VBA - made in .NET "ActiveX component can't create object"

[Guest]

I am going to outline the steps I go through to produce the problem. Hopefully this will help you understand the problem better

I have created a simple COM DLL in .NET by using the COM class template and by setting output to a type library (DLL). All the object does is return a string value. At this point I have not checked the option to register for COM interop in Visual Studio

So I go into Excel (where I want to use the object). Go to VB Editor and Tools -> References and to Add Reference. I browse until I find the \bin directory of the project. Select the .dll file outputted by the program. Then click open -> I get the message "Can't add reference to the specified file." So here is my first question: Why not

So then I go to my project in Visual Studio. Highlight the project -> click properties ->go to Build -> and check "Register for COM interop" -> then I rebuild my solution. After this build there is another file in the \bin directory with the same name as the .dll but has the extension .tlb (Type Library)

So I go back into Excel. Go to VB Editor -> Tools -> References and to Add Reference. I browse until I find the \bin directory of the project. This time however I select the .tlb file outputted by the program. Then click open -> I don’t get an error message so this is good. So here is my second question: Why does the .tlb work and not the .dll at this point

Now after this step. I create the object in VBA code. Compile and run the macro and sure enough it returns the string and puts in the right cell. Great! But wait. Now many more people need this object to work with Excel

So I copy the Excel Workbook and everything in the \bin directory from my project to another person’s computer. Then I open the Excel workbook -> then to VB Editor -> References to double check the path to the .tlb file -> (then compile and save for peace of mind). But here is the big problem... when I run the macro on another person's computer I get the message "ActiveX component can't create object." And I get this at the line where I say: Set testObj = New Object

My ideas... could it be that I am an Admin on my PC but not the other? If so, how do I get around this. Or is Visual Studio doing an extra step in the compile/build process that I’m not replicating on the other PC? Does it need to be an global assembly

Also, .NET is installed on the other PC and I have tried to register the .DLL, but this is unsuccessful as well? I guess that is another question: why

If someone could help me out here I would be very grateful. I have been trying to figure out a solution for a long time. I just don’t understand why everything works fine on my PC but won't run on another

Thanks for being patient
brazilnu

 

[Norman Yuan]

See comments in-line.

I am going to outline the steps I go through to produce the problem.

Hopefully this will help you understand the problem better.

I have created a simple COM DLL in .NET by using the COM class template

and by setting output to a type library (DLL). All the object does is return
a string value. At this point I have not checked the option to register for
COM interop in Visual Studio.
What language do you use to create COM DLL in VS.NET? Unmanaged VC++? As I
know, you cannot make COM component with managed code (VC++, C#, or VB.NET).
From what you described below, it seems that you actually create managed
..NET class, NOT COM class! You cannot use .NET component unless you
export/register it for COM use with REGASM tool (it does the same thing if
you set the option to "register for COM interop" in project properties
dialog box).

So I go into Excel (where I want to use the object). Go to VB Editor and

Tools -> References and to Add Reference. I browse until I find the \bin
directory of the project. Select the .dll file outputted by the program.
Then click open -> I get the message "Can't add reference to the specified
file." So here is my first question: Why not?

Of course not. It is not a COM dll, nor you did not export a COM interface
for the .NET DLL asembly with Regasm tool.

So then I go to my project in Visual Studio. Highlight the project ->

click properties ->go to Build -> and check "Register for COM interop" ->
then I rebuild my solution. After this build there is another file in the
\bin directory with the same name as the .dll but has the extension .tlb
(Type Library).

..tlb file provides the interface information of COM component, so that your
VB program knows what properties and methods of a class object are available
in the dll, but actual work is done with the code in dll. With *.tlb
available, when you do the coding in VB, you can browse the class in Object
Browser window and you get Intelli-sense prompt when entering code in VB
editor. You do not really need the *.tlb if you use late-binding without
setting reference to the component.

So I go back into Excel. Go to VB Editor -> Tools -> References and to Add

Reference. I browse until I find the \bin directory of the project. This
time however I select the .tlb file outputted by the program. Then click
open -> I don't get an error message so this is good. So here is my second
question: Why does the .tlb work and not the .dll at this point?

See above comment

Now after this step. I create the object in VBA code. Compile and run the

macro and sure enough it returns the string and puts in the right cell.
Great! But wait. Now many more people need this object to work with Excel.

So I copy the Excel Workbook and everything in the \bin directory from my

project to another person's computer. Then I open the Excel workbook -> then
to VB Editor -> References to double check the path to the .tlb file ->
(then compile and save for peace of mind). But here is the big problem...
when I run the macro on another person's computer I get the message "ActiveX
component can't create object." And I get this at the line where I say: Set
testObj = New Object.
Now that the .NET component is to be used as COM component, you need
register it as COM component with REGASM tool on each computer that runs
your app. On your development machine, VS.NET already did it for you when
you set the option "Register for COM interop"

My ideas... could it be that I am an Admin on my PC but not the other? If

so, how do I get around this. Or is Visual Studio doing an extra step in the
compile/build process that I'm not replicating on the other PC? Does it need
to be an global assembly?

Also, .NET is installed on the other PC and I have tried to register the

..DLL, but this is unsuccessful as well? I guess that is another question:
why?

Since the component IS .NET class, .NET Framework is required on computers
running your app. You register the .NET component with REGASM tool, not
REGSVR32 (it is for pure COM component).

If someone could help me out here I would be very grateful. I have been

trying to figure out a solution for a long time. I just don't understand why
everything works fine on my PC but won't run on another.

HTH.