Passing arrays from VBA to C# DLL (exposed using DllExport)

Question

I have previously created a C++ DLL that is used by Excel/VBA. It works fine and is utilized to analyze and manipulate data in a workbook.

The current need is to send results to a web API using JSON. This functionality could be implemented directly in VBA, but I'd prefer to use a C# DLL for it. I have existing code to create the JSON message and to authenticate and send it. The missing part is interaction between VBA and C#.

Like with the previous C++ DLL, I want to avoid COM registration and have used RGiesecke.DllExport. Interaction started working right away with integers and doubles and partly with strings. Here are my tests so far:

Functions in C# class:

[DllExport("extAddIntegers")]
    public static int extAddIntegers(int FirstInteger, int SecondInteger)
    {
        return FirstInteger + SecondInteger;
    }

[DllExport("extAddDoubles")]
    public static double extAddDoubles(double FirstDouble, double SecondDouble)
    {
        return FirstDouble + SecondDouble;
    }

[DllExport("extStringLengthAsInteger")]
    public static int extStringLengthAsInteger(string MyString)
    {
         return MyString.Length;
    }

[DllExport("extStringLengthAsText")]
    public static string extStringLengthAsText(string MyString)
    {
         return "String length = " + MyString.Length;
    }

[DllExport("extArrayTest")]
    public static int extArrayTest(double[] DoubleArray)
    {
        return DoubleArray.Count();
    }

VBA declares

Public Declare PtrSafe Function extAddIntegers Lib "ServiceIntegration.dll" (ByVal FirstInteger As Long, ByVal SecondInteger As Long) As Long
Public Declare PtrSafe Function extAddDoubles Lib "ServiceIntegration.dll" (ByVal FirstDouble As Double, ByVal SecondDouble As Double) As Double
Public Declare PtrSafe Function extStringLengthAsInteger Lib "ServiceIntegration.dll" (ByVal MyString As String) As Long
Public Declare PtrSafe Function extStringLengthAsText Lib "ServiceIntegration.dll" (ByVal MyString As String) As String
Public Declare PtrSafe Function extArrayTest Lib "ServiceIntegration.dll" (ByRef DoubleArray As Double) As Long

Test calls from VBA:

MsgBox extAddIntegers(10, 20), vbOKOnly
MsgBox extAddDoubles(10.3, 20.6), vbOKOnly
MsgBox extStringLengthAsInteger("Test_ÖÄÅ_öäå"), vbOKOnly
MsgBox extStringLengthAsText("Test_ÖÄÅ_öäå"), vbOKOnly

Dim MyDoubleArray(5) As Double
MyDoubleArray(1) = 2.3
MsgBox extArrayTest(MyDoubleArray(0)), vbOKOnly

The three first ones work fine, proving that integers and doubles work both ways and strings can be sent from VBA to C#.

The fourth test is strange. The C# function seemingly works and the message box in VBA shows the expected return string, i.e. "String length = 12". However, Excel crashes after that so the returned string is somehow incompatible.

The real issue is passing arrays, even the little test function does not work. When debugging, the DoubleArray parameter remains Null in C# and that causes an exception on the DoubleArray.Count() line. Visual Studio shows the exception but doesn't crash. Excel on the other hand gets upset by not getting an answer and crashes.

I am attempting the extArrayTest(MyDoubleArray(0)) notation because this works with VBA -> C++:

C++ definition
void extDataManipulation(double ArrayA[48][3], double ArrayB[43][3], double ArrayC[41][3], double ArrayD[23][3]);

VBA call:
extDataManipulation ArrayA(0, 0), ArrayB(0, 0), ArrayC(0, 0), ArrayD(0, 0)

Ultimately I would like to send the same two-dimensional arrays of doubles to the C# DLL. The array dimensions, visible in the C++ declaration, are known and fixed. Additionally I would like to send a two-dimensional array of strings, also with known and fixed dimensions.

I've seen some similar questions, but in those the interoperation had been implemented using COM so the solutions weren't quite applicable.

Could this be a marshaling issue? I have seen that discussed in related questions, and even tried some suggestions but to no avail.

Passing arrays from VBA to C# DLL (exposed using DllExport)

0 Answers