Using C# library function in Excel VBA

Question

My C# library (.net 4.5.2) code looks like this:

namespace HelloWorld
{
    public class Hello
    {
        public string HelloUser(string name)
        {
            return "Hello, " + name;
        }
    }
}

I have made the assembly COM visible in AssemblyInfo.cs file using the following code:

[assembly: ComVisible(true)]

I added the reference to the dll in VBA code via Tools ->References and when trying to use the same in excel vba, I am getting Run-time error: 429 (ActiveX component can't create object). What's wrong with my code/call:

Excel VBA (2013)

Option Explicit

Sub tester()

Dim message As String
Dim User As String

User = "Ronnie"
Dim obj As New HelloWorld.Hello
message = obj.HelloUser(User)

End Sub

UPDATE After adding reference via Toos ->References and checking the status using F2

UPDATE #3 Updated the VBA code and still no success. This time the error is:

Run-time error: 429 (ActiveX component can't create object)

Reference the type library (.tlb) from your VBA project, use it as you would any other type library in VBA. Not sure how COM-friendly static might be for this though, and you might want to decorate the Hello class with appropriate attributes. — Mathieu Guindon
– Mathieu Guindon, Commented Mar 11, 2017 at 5:12
@Mat'sMug I have simplified code for my dll to make it non-static. I suspect I am not declaring the function properly in VBA. Is that the right way to declare the function within the class(Hello) and namespace(HelloWorld)? — user869375
– user869375, Commented Mar 11, 2017 at 5:29
You don't Declare it, you go Tools > References... and then browse to locate the type library, which you add as a reference. Then hit F2 to get to the VBE's object browser and see if there's a HelloWorld library loaded, and whether it contains a Hello class with a HelloUser function. If not, you have a problem. If so, then you use it in VBA like any other referenced type library. — Mathieu Guindon
– Mathieu Guindon, Commented Mar 11, 2017 at 5:33
@Mat'sMug I added it using the suggested method and now I see the dll and the class but nothing underneath the class (Hello). — user869375
– user869375, Commented Mar 11, 2017 at 5:39

Mathieu Guindon · Accepted Answer · 2017-03-11 05:49:08Z

4

Your class exposes no interface to COM. You need to decorate your class with a [Guid("some guid")] attribute and give it a [ProgId("Some.Id")] if you want to be able to use it with late binding, and a [ComDefaultInterface(typeof(IHelloWorld))] that formally describes how members are exposed:

[ComVisible(true)]
[InterfaceType(ComInterfaceType.InterfaceIsDual)]
public interface IHelloWorld
{ 
    string HelloUser(string name);
}

[ComVisible(true)]
[ComDefaultInterface(typeof(IHelloWorld))]
public class HelloWorld : IHelloWorld
{
    public string HelloUser(string name)
    {
        return $"Hello, {name}.";
    }
}

answered Mar 11, 2017 at 5:49

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

t3chb0t Over a year ago

I assume this would be an ordinary library project and not any special office template, right? I need to use the clipboard and c# seems to be an easy way out without fiddling with system APIs. Would also suggest choosing any specific .net framework to reduce potential compatibility issues?

Mathieu Guindon Over a year ago

@t3chb0t in theory the latest .net core has full COM interop support (right?), but I haven't tested it. But yeah, it's just a standard library project; you'll want to be mindful of the bitness of the host app that's going to be referencing the library; might need separate x86 and x64 builds.

t3chb0t Over a year ago

Outch! I've just tried it and it turns out that this requires to register the lib as an administrator. They'll never allow me do do it hehe, back to system apis :-]

t3chb0t Over a year ago

New idea... maybe I can create an addin and call it from VBA. Researching...

Collectives™ on Stack Overflow

Using C# library function in Excel VBA

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related