Return string[] in C# dll for VBA

Question

I've written this little C# test DLL using UnmanagedExports (obtained as a NuGet package), which is working fine. However, I'm wondering if, and if so how, it's possible to immediately return a String() array instead of returning a string which has to be Split() in a VBA wrapper function.

That is to say, the point of interest is the method GetFilesWithExtension(). The other methods in the dll are just small tests I made while figuring out how to pass strings with the correct encoding.

The DLL targets x64 and .NET 4.5.2, but you should be able to build for x86 as well (and accordingly change the Function Declares in VBA).

C# class library (TestDll.dll):

using System;
using System.IO;
using System.Linq;
using System.Runtime.InteropServices;
using RGiesecke.DllExport;

namespace TestDll
{
    public class Class1
    {
        [DllExport(nameof(Addition), CallingConvention.StdCall)]
        public static int Addition(int a, int b)
        {
            return a + b + 100;
        }


        [DllExport(nameof(LinqAddition), CallingConvention.StdCall)]
        public static int LinqAddition(int a, int b)
        {
            return new int[] {a, b, 1, 4, 5, 6, 7, 8 }.Sum();
        }

        [DllExport(nameof(LinqAdditionString), CallingConvention.StdCall)]
        [return: MarshalAs(UnmanagedType.AnsiBStr)]
        public static string LinqAdditionString(int a, int b)
        {
            return new int[] { a, b, 1, 4, 5, 6, 7, 8 }.Sum() + "";
        }

        [DllExport(nameof(GetFilesWithExtension), CallingConvention.StdCall)]
        [return: MarshalAs(UnmanagedType.AnsiBStr)]
        public static string GetFilesWithExtension([MarshalAs(UnmanagedType.AnsiBStr)] string folderPath, [MarshalAs(UnmanagedType.AnsiBStr)] string extension, bool includeSubdirectories)
        {
            //Debug
            //File.WriteAllText(@"C:\Users\johanb\Source\Repos\TestDll\output.txt", $"folderPath: {folderPath}, extension: {extension}, includeSubdirectories: {includeSubdirectories}");
            try
            {
                if (!Directory.Exists(folderPath))
                    return "";

                extension = extension.Trim('.');

                return string.Join(";",
                    Directory.GetFiles(folderPath, "*.*",
                            includeSubdirectories ? SearchOption.AllDirectories : SearchOption.TopDirectoryOnly)
                        .Where(
                            f =>
                                Path.GetExtension(f)?
                                    .Trim('.')
                                    .Equals(extension, StringComparison.InvariantCultureIgnoreCase) ?? false)
                        .ToArray());
            }
            catch (Exception ex)
            {
                return ex.ToString();
            }
        }
    }
}

VBA module (tested in Excel):

Attribute VB_Name = "TestDll"
Option Explicit

Public Declare PtrSafe Function Addition Lib "C:\Users\johanb\Source\Repos\TestDll\TestDll\bin\Debug\TestDll.dll" (ByVal a As Long, ByVal b As Long) As Long
Public Declare PtrSafe Function LinqAddition Lib "C:\Users\johanb\Source\Repos\TestDll\TestDll\bin\Debug\TestDll.dll" (ByVal a As Long, ByVal b As Long) As Long
Public Declare PtrSafe Function LinqAdditionString Lib "C:\Users\johanb\Source\Repos\TestDll\TestDll\bin\Debug\TestDll.dll" (ByVal a As Long, ByVal b As Long) As String
Public Declare PtrSafe Function GetFilesWithExt Lib "C:\Users\johanb\Source\Repos\TestDll\TestDll\bin\Debug\TestDll.dll" Alias "GetFilesWithExtension" (ByVal folderPath As String, ByVal extension As String, ByVal includeSubdirs As Boolean) As String

Sub Test()
    Dim someAddition As Long
    Dim someLinqAddition As Long
    Dim someLinqAdditionAsString As String
    Dim files() As String
    Dim i As Long

    someAddition = Addition(5, 3)
    Debug.Print someAddition

    someLinqAddition = LinqAddition(5, 3)
    Debug.Print someLinqAddition

    someLinqAdditionAsString = LinqAdditionString(5, 3)
    Debug.Print someLinqAddition

    files = GetFilesWithExtension("C:\Tradostest\Project 4", "sdlxliff", True)
    For i = 0 To UBound(files)
        Debug.Print files(i)
    Next i

End Sub

Function GetFilesWithExtension(folderPath As String, extension As String, includeSubdirs As Boolean) As String()
    GetFilesWithExtension = Split(GetFilesWithExt(folderPath, extension, includeSubdirs), ";")
End Function

Answer 1

I could never quite get returning an object to Excel to work, but passing an object by reference back and forth works just fine. For whatever reason, I had to use the keyword ref instead of out, otherwise Excel would crash.

I also had to use UnmanagedType.AnsiBstr for strings to get the encoding right, but for string arrays, the only way I could get it to work was declaring it as an object and do the type checking at run time at the start of the method.

using System;
using System.IO;
using System.Linq;
using System.Runtime.InteropServices;
using RGiesecke.DllExport;

namespace TestDll
{
    public class FolderHandling
    {
        [DllExport(nameof(GetFilesByExtensions), CallingConvention.StdCall)]
        [return: MarshalAs(UnmanagedType.Bool)]
        public static bool GetFilesByExtensions(
            ref object arrayOfFiles,                                  //out doesn't work
            [MarshalAs(UnmanagedType.AnsiBStr)] string folderPath,
            object extensions,                                       //type safety breaks it..somehow
            [MarshalAs(UnmanagedType.Bool)] bool includeSubdirectories)
        {
            try
            {
                if (!Directory.Exists(folderPath))
                {
                    arrayOfFiles = new[] { $"Parameter {nameof(folderPath)} ({folderPath}) is not a folder" };
                    return false;
                }

                if (!(extensions is string[]))
                {
                    arrayOfFiles = new[] { $"Parameter {nameof(extensions)} is not a string array" };
                    return false;
                }

                var exts = ((string[])extensions).Select(e => e.Trim('.').ToLowerInvariant()).ToArray();

                var files = Directory.GetFiles(folderPath, "*.*",
                        includeSubdirectories ? SearchOption.AllDirectories : SearchOption.TopDirectoryOnly)
                    .Where(f => exts.Contains(Path.GetExtension(f)?.Trim('.').ToLowerInvariant() ?? ";;;"))
                    .ToArray();


                //normalize ANSI just in case
                General.NormalizeANSI(ref files);

                arrayOfFiles = files;

                return true;
            }
            catch (Exception ex)
            {
                arrayOfFiles = new[] { "Exception: " + ex };
                return false;
            }
        }
    }
}


using System;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;

namespace TestDll
{
    static class General
    {
        public static void NormalizeANSI(ref string[] files)
        {
            for (int i = 0; i < files.Length; i++)
            {
                files[i] = string.Concat(files[i].Normalize(NormalizationForm.FormD).Where(c => CharUnicodeInfo.GetUnicodeCategory(c) != UnicodeCategory.NonSpacingMark));
            }
        }
    }
}

I can use my DLL in Excel as below, using LoadLibrary(), so that I don't have to place it in the user's system folder or register for COM. The advantage of using FreeLibrary() is that it allows me to recompile the C# project without closing Excel.

Public Declare PtrSafe Function GetFilesByExtensions Lib "TestDll.dll" (ByRef filesRef, ByVal folderPath As String, ByVal extensions, ByVal includeSubdirs As Boolean) As Boolean

Private Declare PtrSafe Function FreeLibrary Lib "kernel32" (ByVal hLibModule As Long) As Long
Private Declare PtrSafe Function LoadLibraryA Lib "kernel32" (ByVal lpLibFileName As String) As Long

Private Function LoadLibrary(dllName As String) As Long
    Dim path As String
    path = ThisWorkbook.path & "\" & dllName
    LoadLibrary = LoadLibraryA(path)
End Function

Sub TestFolderFiltering()
    Dim files() As String
    Dim i As Long
    Dim moduleHandle As Long

    On Error GoTo restore

    moduleHandle = LoadLibrary("TestDll.dll")

    If GetFilesByExtensions(files, "C:\Tradostest\Project 4", Split("sdlxliff", ";"), True) Then
        For i = 0 To UBound(files)
            Debug.Print " - " & files(i)
        Next i
    Else
        Debug.Print "ERROR: " & files(0)
    End If

restore:
    If moduleHandle <> 0 Then
        Call FreeLibrary(moduleHandle)
    End If

End Sub

It is also possible to pass COM objects from VBA to a DLL like this and process them using the standard Microsoft Interop libraries or NetOffice, and I've managed to write a method that filters VBA string arrays by the string representation of a C# lambda expression, which sounds like it might come in handy for many people:

If FilterStringArray(myArr, "s => s.ToUpperInvariant().Equals(s, StringComparison.CurrentCulture)") Then
    For i = 0 To UBound(myArr)
        Debug.Print " - " & myArr(i)
    Next i
Else
    Debug.Print "ERROR: " & myArr(0)
End If

You can find the entire project on GitLab.