2

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?

5
  • 1
    You are returning an array of Strings rather than objects to why declare as an array of objects? Try: string[] StringCleaner(object lineOut, object headerOut); Commented Oct 26, 2015 at 10:16
  • I tried that @Meehow, but that will give me a runtime error in Excel: Can't assign to array, even when I declare a new variant array to assign the return array to: Dim tmp() As Variant tmp = lib.StringCleaner(arrLineOut, dicInToOut.Items) Commented Oct 26, 2015 at 12:41
  • That's strange. Try something very simple like public string[] GetArrayFromCSharp() { return new List<string> {"foo", "boo"}.ToArray(); } and in VBA Dim arr As Variant arr = lib.GetArrayFromCSharp() Debug.Print arr(0), arr(1) Commented Oct 26, 2015 at 13:26
  • That actually worked! I now changed my code line return MC.Generic.StringCleaner(ComObjectToStringList(lineOut), ComObjectToIntList(headerOut)).ToArray<string>(); to return MC.Generic.StringCleaner(ComObjectToStringList(lineOut), ComObjectToIntList(headerOut)).ToArray(); so just omitting the <string> at the end as per your sample code and voila: I can now assign the array to a newly declared variant array in VBA. Thanks @Meehow! Commented Oct 26, 2015 at 15:16
  • @Gess - please answer yourself and close this question Commented Oct 26, 2015 at 20:38

1 Answer 1

2

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)
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.