Passing an array of objects from C# to VBA Excel

Question

I have a C# console application and Dll. I registered the dll as COM via interop. I used the

1. COM Visible property, GUID etc
2. Set Assembly Information, Build information to register it to COM

I referenced this dll in the VBA code.

I created a list of objects for that class in the console application, converted it to Array and then used Excel.Run to send the array of user defined objects to VBA code.

I can access properties of the array in VBA like LBound and UBound. But I am unable to access each individual object in the array.

The C# code is as follows:

using System;
using System.Runtime.InteropServices;

namespace Save_as_excel_classes
{
    [Guid("0BA8F8DE-8F0A-4D7E-9DDB-8AED42943BDA")]
    [ComVisible(true)]
    [ClassInterface(ClassInterfaceType.AutoDual)]
    public class CollClass
    {
        [ComVisible(true)]
        public string NameValue { get; set; }
    }
}

The Console App is as follows:

using Microsoft.Office.Interop.Excel;
using Save_as_excel_classes;
using System;
using System.Collections;
using System.Collections.Generic;
using _Excel = Microsoft.Office.Interop.Excel;

namespace ConsoleApp1
{
    internal class Program
    {
        static void Main(string[] args)
        {
            Application excel = new _Excel.Application();
            try
            {
                string fileName = "D:\\Book2.xlsm";
                Workbook wb;
                Worksheet ws;
                int sheetNumber = 1;
                excel.Visible = true;
                wb = excel.Workbooks.Open(fileName);
                ws = wb.Worksheets[sheetNumber];
                ws = wb.Worksheets[sheetNumber];
              
                var collVals = new List<CollClass>();
                
                collVals.Add(new CollClass() { NameValue = "ABC" });
                collVals.Add(new CollClass() { NameValue = "DEF" });
                collVals.Add(new CollClass() { NameValue = "GHI" });
                collVals.Add(new CollClass() { NameValue = "KLM" });
                CollClass[] arr = collVals.ToArray();
                excel.Run("ThisWorkbook.GetListofObjects1", arr);
            }
            catch (Exception ex)
            {

            }
            finally
            {
                excel.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
                System.Runtime.InteropServices.Marshal.FinalReleaseComObject(excel);
            }
        }
    }
}

The VBA side looks like this: I am just trying to set the NameValue to a cell.

Public Function GetListofObjects1(ByRef objColl() As collClass)
    MsgBox "Inside GetListofObjects function" + " Hurray!"
    Range("C3").Value = LBound(objColl)
    Range("C4").Value = UBound(objColl)
    Range("C5").Value = objColl(0).NameValue
End Function

I've searched various questions on StackOverflow and forums but I don't know what I am doing wrong. Every time I run the console application, Excel opens, I get the message box and then crashes and restarts. I also get the exception in the c# console application as follows after the LBound line and the UBound line execute. This only happens for the Range("C5").Value = objColl(0).NameValue

"System.Runtime.InteropServices.COMException: 'Exception occurred. (Exception from HRESULT: 0x80020009 (DISP_E_EXCEPTION))'"

I get the proper NameValue when I send a single object instead of an array. It's just with the array that all the problems seem to occur.

This probably has a very simple solution but I've been at this for over a day and a half now and I can't seem to figure it out.

Edit:

I added another class CollClassArray to the dll that will just contain the one CollClass array. When I pass the object of CollClassArray through the Run command and try to access the array inside it I get the error: Wrong number of arguments or invalid use of property near the CollArray(0).

Public Function GetListOfObjects1(array as CollClassArray)
Dim objColl as CollClass
Set objColl = array.CollArray(0).NameValue
End Function

Answer 1

After researching a bit more, I added mscorlib as a reference in the VBA code. I used the mscorlib.ArrayList and was able to access the elements in the ArrayList.

c# code: with ArrayList

 static void Main(string[] args)
 {
     Application excel = new _Excel.Application();
     try
     {
         string fileName = "D:\\Book2.xlsm";
         dynamic wb;
         Worksheet ws;
         int sheetNumber = 1;
         excel.Visible = true;
         wb = excel.Workbooks.Open(fileName);
         ws = wb.Worksheets[sheetNumber];
         
         var collVals = new List<CollClass>();
         
         collVals.Add(new CollClass() { NameValue = "ABC", NumberValue = 2 });
         collVals.Add(new CollClass() { NameValue = "DEF", NumberValue = 4 });
         collVals.Add(new CollClass() { NameValue = "GHI", NumberValue = 6 });
         collVals.Add(new CollClass() { NameValue = "KLM", NumberValue = 8 });
         CollClass[] arr = collVals.ToArray();

        
         ArrayList list = new ArrayList();
         list.AddRange(arr);
         excel.Run("ThisWorkbook.SetC5", list);
     }
     catch (Exception ex)
     {

     }
     finally
     {
         
         excel.Quit();
         System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
         System.Runtime.InteropServices.Marshal.FinalReleaseComObject(excel);
     }
 }

VBA code:

Public Function setC5(coll As mscorlib.ArrayList)
    MsgBox "Inside this function"
    Dim count As Integer
    Dim coll2(), coll1 As collClass
    Dim number As Integer
    number = coll.count
    Range("C6") = number
    Set coll1 = coll(0)
    Range("C5").Value = coll1.NameValue
End Function