如何无需传递对象，用Sub填充VBA全局Collection？

问题根源 #

你用Public myCol As New Collection的声明方式存在陷阱：VBA对As New采用延迟初始化机制——每次引用myCol时，VBA会自动检查它是否为Nothing，如果是就新建一个实例。这会导致在某些操作后（比如错误处理、其他过程的误操作），原集合实例被意外替换成新的空集合，之前填充的内容自然丢失。

解决方案 #

1. 正确声明全局集合

在标准模块顶部仅声明集合，不自动初始化：

Public myCol As Collection
' 可同时声明多个全局集合
Public myOtherCol As Collection

2. 手动初始化集合

在程序启动时（比如Workbook_Open事件，或专门的初始化过程）手动初始化集合，确保实例唯一：

Sub InitGlobalCollections()
    ' 初始化myCol
    If myCol Is Nothing Then
        Set myCol = New Collection
    End If
    
    ' 同时初始化其他全局集合
    If myOtherCol Is Nothing Then
        Set myOtherCol = New Collection
    End If
End Sub

3. 直接在子过程中填充全局集合

现在可以在任意子过程中直接操作全局集合，无需传递对象，同时填充多个集合也很方便：

Sub PopulateGlobalCollections()
    ' 双重保险：确保集合已初始化
    If myCol Is Nothing Then Set myCol = New Collection
    If myOtherCol Is Nothing Then Set myOtherCol = New Collection
    
    ' 可选：清空旧内容（根据业务需求决定）
    ' Do While myCol.Count > 0
    '     myCol.Remove 1
    ' Loop
    
    ' 填充myCol
    myCol.Add "第一组内容1"
    myCol.Add "第一组内容2"
    
    ' 填充myOtherCol
    myOtherCol.Add "第二组内容1"
    myOtherCol.Add "第二组内容2"
End Sub

4. 验证集合内容

写个测试过程确认内容是否保留：

Sub CheckCollections()
    ' 检查myCol
    If Not myCol Is Nothing Then
        Debug.Print "myCol内容："
        Dim i As Integer
        For i = 1 To myCol.Count
            Debug.Print "  " & myCol(i)
        Next i
    Else
        Debug.Print "myCol未初始化"
    End If
    
    ' 检查myOtherCol
    If Not myOtherCol Is Nothing Then
        Debug.Print vbCrLf & "myOtherCol内容："
        For i = 1 To myOtherCol.Count
            Debug.Print "  " & myOtherCol(i)
        Next i
    Else
        Debug.Print vbCrLf & "myOtherCol未初始化"
    End If
End Sub

关键说明 #

• 避免用Public ... As New ...声明全局对象，手动控制初始化能彻底避免实例被意外重建的问题。
• 全局集合在整个VBA项目中可见，可在任意子过程/函数中直接操作，无需传递对象参数，完全满足你“单个子例程填充多个全局集合”的需求。

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