C#向VBA Excel传递自定义对象数组失败问题排查

问题根源 #

直接传递.NET自定义对象数组到VBA时，COM互操作会将数组包装为System.Array类型，而非VBA能识别的强类型自定义对象数组。虽然VBA能获取数组的LBound/UBound，但访问元素时无法正确解析对象类型，导致崩溃。单独传递单个对象时，COM能正确暴露对象的接口，所以可以正常访问属性。

修复步骤 #

1. 修改C# COM组件，添加自定义集合类 #

在Save_as_excel_classes命名空间中添加实现IEnumerable的集合类，同时提供索引器，确保COM可见：

using System;
using System.Collections;
using System.Collections.Generic;
using System.Runtime.InteropServices;

namespace Save_as_excel_classes
{
    [Guid("F5A2B1C7-3D9E-4F8A-9B0C-1D2E3F4A5B6C")] // 生成新的唯一GUID
    [ComVisible(true)]
    [ClassInterface(ClassInterfaceType.AutoDual)]
    public class CollClassCollection : IEnumerable
    {
        private List<CollClass> _items = new List<CollClass>();

        [ComVisible(true)]
        public void Add(CollClass item)
        {
            _items.Add(item);
        }

        [ComVisible(true)]
        public CollClass this[int index]
        {
            get { return _items[index]; }
            set { _items[index] = value; }
        }

        [ComVisible(true)]
        public int Count => _items.Count;

        public IEnumerator GetEnumerator()
        {
            return _items.GetEnumerator();
        }
    }
}

2. 更新C#控制台应用代码 #

将List<CollClass>转为自定义集合，而非数组，传递给VBA：

// 替换原数组转换代码
var collVals = new CollClassCollection();
collVals.Add(new CollClass() { NameValue = "ABC" });
collVals.Add(new CollClass() { NameValue = "DEF" });
collVals.Add(new CollClass() { NameValue = "GHI" });
collVals.Add(new CollClass() { NameValue = "KLM" });

// 传递集合对象给VBA
excel.Run("ThisWorkbook.GetListofObjects1", collVals);

3. 修改VBA代码 #

将参数类型改为自定义集合，通过索引访问元素：

Public Function GetListofObjects1(ByRef objColl As CollClassCollection)
    MsgBox "Inside GetListofObjects function" + " Hurray!"
    Range("C3").Value = 0 ' 集合默认从0开始索引
    Range("C4").Value = objColl.Count - 1
    Range("C5").Value = objColl(0).NameValue
    ' 遍历所有元素示例
    Dim i As Integer
    For i = 0 To objColl.Count - 1
        Range("C" & 6 + i).Value = objColl(i).NameValue
    Next i
End Function

4. 重新注册COM组件 #

修改DLL后，重新执行注册命令：

regasm /codebase Save_as_excel_classes.dll

关于包装数组类出错的说明 #

你之前尝试的CollClassArray类，其数组属性在COM中会暴露为get_CollArray方法，VBA访问时需要使用obj.CollArray.Item(0)而非obj.CollArray(0)。但自定义集合类的索引器更符合VBA的使用习惯，兼容性更好。

内容的提问来源于stack exchange，提问作者Dee