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, Mar 11 '17 at 05: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, Mar 11 '17 at 05:29
http://stackoverflow.com/questions/24193183/can-i-call-a-static-method-of-a-c-sharp-class-from-vba-via-com — Robin Mackenzie, Mar 11 '17 at 05: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, Mar 11 '17 at 05: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, Mar 11 '17 at 05:39

score 4 · Accepted Answer · answered Mar 11 '17 at 05:49

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 '17 at 05:49

Mathieu Guindon

69,817
8
107
235

I assume this would be an ordinary library project and not any special office template, right? I need to use the clipboard and [tag: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? – t3chb0t Jul 09 '20 at 17:17
1

@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. – Mathieu Guindon Jul 09 '20 at 17:36
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 Jul 09 '20 at 18:14
New idea... maybe I can create an addin and call it from VBA. Researching... – t3chb0t Jul 09 '20 at 18:38

Using C# library function in Excel VBA

1 Answers1