How do I use functions from a managed DLL in VBA?

Question

I have a simple C# function I wish to use from VBA. For the question's sake, lets say the routine in C# looks like this:

using System;
namespace MyTestLibrary
{
    public static class Class1
    {
        static string Shout(string message)
        {
            return message + "!";
        }
    }
}

I want to be able to use it on the VBA end like so. This causes an error ("Cannot find DLL entry point "Shout"):

Public Declare Function Yell Lib "C:\blah\MyTestLibrary.dll" Alias "Shout" (phrase As String) As String

Sub test()
    MsgBox Yell("Hello World")
End Sub

Further research showed a workaround as (and I have gotten this implementation to work) :

Sub test()
    Dim y As MyTestLibrary.Class1
    Set y = New MyTestLibrary.Class1
    MsgBox y.Shout("Hello World")
End Sub

However, this does not meet my requirements as I cannot deploy my library alongside my workbook as I must manually add the reference. Not a big deal for individual use, but a headache when you have to deploy it to non-tech oriented end users, as each dll must be referenced manually.

TLDR How do I use a static functions made with C# in VBA?

possible duplicate of [Using a C# dll inside EXCEL VBA](http://stackoverflow.com/questions/6340041/using-a-c-sharp-dll-inside-excel-vba) — GSerg, Dec 19 '11 at 20:29

score 3 · Accepted Answer · answered Dec 19 '11 at 20:34

3

I think this is just not possible. You need to create a COM wrapper in C# or VB.Net with an equivalent non-static function and call that instead from your VBA program.

answered Dec 19 '11 at 20:34

yms

10,361
3
38
68

The [link provided by GSerg](http://stackoverflow.com/questions/6340041/using-a-c-sharp-dll-inside-excel-vba) should get you started with the COM approach. – yms Dec 19 '11 at 20:35
1

Actually, [that guy](http://stackoverflow.com/users/35443/robert-giesecke) there offers a template for creating exported functions with C# that are usable with `declare function` from VBA. It's still COM interop, but with a twist and without the burdensome registration. – GSerg Dec 19 '11 at 20:39
@GSerg I did not follow the link at first, now I did, and it is indeed impressive! Thanks. – yms Dec 19 '11 at 20:47

score 0 · Answer 2 · answered Dec 20 '11 at 00:57

0

Look at the Forms Interop project, and maybe you can figure it out. If you can call a form from VB6/VBA you can call a function.

link: http://www.codeproject.com/KB/dotnet/VB6_-_C__Interop_Form.aspx

answered Dec 20 '11 at 00:57

John Alexiou

28,472
11
77
133

How do I use functions from a managed DLL in VBA?

2 Answers2