title: Return string[] in C# dll for VBA
body: <p>I've written this little C# test DLL using <a href="https://sites.google.com/site/robertgiesecke/Home/uploads/unmanagedexports" rel="nofollow">UnmanagedExports</a> (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.</p>
  
  <p>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.</p>
  
  <p>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).</p>
  
  <p><strong>C# class library (TestDll.dll):</strong></p>
  
  <pre><code>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 =&gt;
                                  Path.GetExtension(f)?
                                      .Trim('.')
                                      .Equals(extension, StringComparison.InvariantCultureIgnoreCase) ?? false)
                          .ToArray());
              }
              catch (Exception ex)
              {
                  return ex.ToString();
              }
          }
      }
  }
  </code></pre>
  
  <p><strong>VBA module (tested in Excel):</strong></p>
  
  <pre><code>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
  </code></pre>