VBA中如何在调用Add方法时将数组存入Collection集合

VBA 不支持直接用括号内联构造数组作为Collection.Add的参数，使用内置Array()函数即可实现和Python字典内联写键值对完全一致的效果，无需提前定义可复用的数组变量，配合简单的封装就能实现全局单次初始化、全程序调用的需求。

具体实现代码 #

在VBA工程中插入一个标准模块，粘贴以下代码：

' 声明全局级别的参考数据集合
Public g_RefData As Collection

' 对外提供集合访问的方法，自动完成单次初始化
Public Function RefData() As Collection
    ' 仅首次调用时执行初始化逻辑
    If g_RefData Is Nothing Then
        Set g_RefData = New Collection
        ' 直接内联写入参考数据，无需单独定义数组变量
        g_RefData.Add Array(1, "B"), "B"
        g_RefData.Add Array(1.014, "FeC"), "Fe"
        ' 剩余约100条参考数据按相同格式续写即可
    End If
    Set RefData = g_RefData
End Function

调用方式 #

程序任意位置需要读取参考数据时，直接通过键名检索即可，不需要维护冗长的If/ElseIf分支：

Sub TestDemo()
    ' 读取键"Fe"对应的参考数组
    Dim item As Variant
    item = RefData()("Fe")
    
    Debug.Print item(0) ' 输出数值1.014
    Debug.Print item(1) ' 输出字符串"FeC"
End Sub

注意事项 #

• Array()函数构造的是Variant类型数组，默认下标从0开始，访问逻辑和Python元组一致
• 上述封装逻辑会保证集合在程序运行周期内仅初始化一次，不会出现重复创建、数据覆盖的问题
• 所有参考数据集中在初始化代码块中，增删条目只需要修改对应Add行即可，维护成本远低于分支判断
• 键名和数组内存储的内容没有强制绑定关系，可以根据实际业务需求调整数组内的元素数量、类型

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