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?

Answer 1

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.