Return Array from C# COM Library to VBA

Question

I've spent quite some time trying to find a solution but I just can't seem to get it to work. The situation is as follows:

I'm setting up a c# com library to be used by a vba macro. The c# library method in question takes two arrays as parameters and returns an array identical in size and type to the first parameter. Here's the c# code (Interface and implementation):

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Runtime.InteropServices;
using System.Reflection;
using System.Windows.Forms;

namespace Aufbereiten.Base.Controller
{
    [Guid("A341A58B-96CA-42B8-8256-B8AC16586176")]
    public interface IExposer
    {
        [DispId(1)]
        void Connect(String macroName, String inputFileName, String functionType);
        [DispId(2)]
        Boolean Run();
        [DispId(3)]
        object[] StringCleaner(object lineOut, object headerOut);
    }

    [Guid("C66FF152-60E2-4AF3-9D65-655C224A241E")]
    [Serializable(), ClassInterface(ClassInterfaceType.None), ComVisible(true)]
    public class Library : IExposer
    {
        #region Fields
        private MainController _mc;
        #endregion

        #region Properties
        public MainController MC
        {
            get { return _mc; }
            set { _mc = value; }
        }
        #endregion

        #region Methods
        public void Connect(String macroName, String fileName, String functionType)
        {
            MC = new MainController(macroName, fileName, functionType);
        }

        public Boolean Run()
        {
            return MC.Run();
        }

        /// <summary>
        /// Cleans input string of unwanted characters
        /// </summary>
        /// <param name="lineOut">String Array</param>
        /// <param name="headerOut">Integer Array</param>
        /// <returns>String array</returns>
        public object[] StringCleaner(object lineOut, object headerOut)
        {
            // Convert input objects to List and convert return List to string array directly
            return MC.Generic.StringCleaner(ComObjectToStringList(lineOut), ComObjectToIntList(headerOut)).ToArray<string>();
        }

        /// <susgbomary>
        /// Converts a com object into a string list
        /// </summary>
        /// <param name="comObj">object</param>
        /// <returns>String list</returns>
        private List<string> ComObjectToStringList(object comObj)
        {
            List<string> result = new List<string>();

            // Convert COM object (VBA array) to string list
            Type comObjType = comObj.GetType();

            object[] args = new object[1];
            int numEntries = (int)comObjType.InvokeMember("Length", BindingFlags.GetProperty, null, comObj, null);
            for (int i = 0; i < numEntries; i++)
            {
                args[0] = i;
                string currentEntry = (string)comObjType.InvokeMember("GetValue", BindingFlags.InvokeMethod, null, comObj, args);
                if (currentEntry == null)
                {
                    result.Add("");
                }
                else
                {
                    result.Add(currentEntry);
                }
            }

            return result;
        }

        /// <summary>
        /// Converts a com object into an int list
        /// </summary>
        /// <param name="comObj">object</param>
        /// <returns>Int32 list</returns>
        private List<int> ComObjectToIntList(object comObj)
        {
            List<int> result = new List<int>();

            // Convert COM object (VBA array) to int list
            Type comObjType = comObj.GetType();

            object[] args = new object[1];
            int numEntries = (int)comObjType.InvokeMember("Length", BindingFlags.GetProperty, null, comObj, null);
            for (int i = 0; i < numEntries; i++)
            {
                args[0] = i;
                result.Add((int)comObjType.InvokeMember("GetValue", BindingFlags.InvokeMethod, null, comObj, args));
            }

            return result;
        }
        #endregion
    }
}

The Generic.StringCleaner method will do the actual work of cleaning the parameter array "lineOut" of unwanted characters. The following is an extract of the VBA code calling the library method:

Dim arrLineOut() As Variant
Dim dicInToOut As New Scripting.Dictionary

...

Dim lib As New Aufbereiten.library
lib.Connect ThisWorkbook.Name, "_Library_Test_custFile.xlsx", "CustImport"
arrLineOut = lib.StringCleaner(arrLineOut, dicInToOut.Items)

All the operations inside the c# run flawlessly except the returning of the result, the cleaned array.

When I run the macro I get a

runtime error 80131533: A mismatch has occurred between the runtime type of the array and the sub type recorded in the metadata.

Could anyone please enlighten me what I'm doing wrong here?

Answer 1

As per advice from @Meehow I've adjusted

public object[] StringCleaner(object lineOut, object headerOut)

to

public string[] StringCleaner(object lineOut, object headerOut)

and

return MC.Generic.StringCleaner(ComObjectToStringList(lineOut), ComObjectToIntList(headerOut)).ToArray<string>();

to

return MC.Generic.StringCleaner(ComObjectToStringList(lineOut), ComObjectToIntList(headerOut)).ToArray();

After these changes I was able to assign the return value from the c# library to a newly declared variant array in VBA:

Dim tmp As Variant
tmp = lib.StringCleaner(arrLineOut, dicInToOut.Items)