VBA在c# dll中返回字符串[]

我从来没有得到返回一个对象到Excel工作，但通过引用来回传递对象工作得很好。无论出于什么原因，我必须使用关键字ref而不是out，否则Excel会崩溃。

我还必须使用UnmanagedType。AnsiBstr用于字符串以获得正确的编码，但对于字符串数组，我可以让它工作的唯一方法是将其声明为对象，并在运行时在方法开始时进行类型检查。

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));
            }
        }
    }
}

我可以使用我的DLL在Excel如下，使用LoadLibrary()，这样我就不必把它放在用户的系统文件夹或注册COM。使用FreeLibrary()的好处是，它允许我重新编译c#项目而不关闭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

也可以将COM对象从VBA传递到像这样的DLL，并使用标准的微软互操作库或NetOffice处理它们，并且我已经设法编写了一个方法，通过c# lambda表达式的字符串表示过滤VBA字符串数组，这听起来可能对许多人有用:

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

你可以在GitLab上找到整个项目