使用.NET 3.5 Hashtable时Excel VBA对象无法正常释放的问题

问题说明 #

使用.NET 3.5的System.Collections.Hashtable或SortedList添加VBA类实例后，即便调用Clear、释放集合对象，VBA类的Terminate事件也不会触发，说明对象未被释放，出现了内存泄漏。VBA自带的Dictionary能正常释放对象，但找不到替代SortedList的合适方案。

测试代码 #

最简测试类（TestObject）

Option Explicit

Private Sub Class_Initialize()
    Debug.Print "Initialize"
End Sub

Private Sub Class_Terminate()
    Debug.Print "Terminate"
End Sub

正常释放的对照测试

Option Explicit

Sub Test()
    Dim oTestObject As TestObject
    Dim oTestObject2 As TestObject
    
    Set oTestObject = New TestObject
    Set oTestObject2 = oTestObject
    
    Set oTestObject = Nothing
    Set oTestObject2 = Nothing
End Sub

运行后即时窗口先显示Initialize，最后显示Terminate，对象释放正常。

Hashtable泄漏测试

Option Explicit

Sub Test()
    Dim oTestObject As TestObject
    Dim oTestObject2 As TestObject
    Dim ht As Hashtable
    
    Set oTestObject = New TestObject
    Set oTestObject2 = oTestObject

    Set ht = New Hashtable
    ht.Add "key", oTestObject
    
    Set oTestObject = Nothing
    Set oTestObject2 = Nothing
    
    ht.Clear
    Set ht = Nothing
End Sub

运行后即时窗口始终不显示Terminate，对象未被释放。

SortedList同样泄漏

Option Explicit

Sub Test()
    Dim oTestObject As TestObject
    Dim oTestObject2 As TestObject
    Dim sl As SortedList
    
    Set oTestObject = New TestObject
    Set oTestObject2 = oTestObject
    Set sl = New SortedList
    
    sl.Add "key", oTestObject
    
    Set oTestObject = Nothing
    Set oTestObject2 = Nothing
    sl.Clear
    Set sl = Nothing
End Sub

同样无法触发Terminate事件。

解决办法 #

1. 手动解除集合对VBA对象的引用 #

在调用Clear或释放集合之前，先把集合中对应键的对象显式设为Nothing，强制断开.NET集合的引用：

修复Hashtable的代码

Sub TestFixedHashtable()
    Dim oTestObject As TestObject
    Dim oTestObject2 As TestObject
    Dim ht As Hashtable
    
    Set oTestObject = New TestObject
    Set oTestObject2 = oTestObject

    Set ht = New Hashtable
    ht.Add "key", oTestObject
    
    Set oTestObject = Nothing
    Set oTestObject2 = Nothing
    
    ' 先手动把集合里的对象置空
    ht("key") = Nothing
    ht.Clear
    Set ht = Nothing
End Sub

修复SortedList的代码

Sub TestFixedSortedList()
    Dim oTestObject As TestObject
    Dim oTestObject2 As TestObject
    Dim sl As SortedList
    
    Set oTestObject = New TestObject
    Set oTestObject2 = oTestObject
    Set sl = New SortedList
    
    sl.Add "key", oTestObject
    
    Set oTestObject = Nothing
    Set oTestObject2 = Nothing
    
    ' 手动置空引用
    sl("key") = Nothing
    sl.Clear
    Set sl = Nothing
End Sub

操作后即时窗口会正常显示Terminate，对象被正确回收。

2. 自定义VBA版有序列表替代SortedList #

如果不想每次手动置空，可以自己实现一个基于VBA的有序列表，确保引用能被正确释放：

Class SortedVBAList
    Private m_items As Collection
    
    Private Sub Class_Initialize()
        Set m_items = New Collection
    End Sub
    
    Private Sub Class_Terminate()
        ' 销毁时手动释放所有对象引用
        Dim item As Variant
        For Each item In m_items
            Set item(1) = Nothing
        Next
        Set m_items = Nothing
    End Sub
    
    Public Sub Add(key As String, value As Variant)
        ' 插入时保持键的升序排列
        Dim i As Integer
        For i = 1 To m_items.Count
            Dim existingKey As String
            existingKey = m_items(i)(0)
            If key < existingKey Then
                m_items.Add Array(key, value), Before:=i
                Exit Sub
            End If
        Next
        m_items.Add Array(key, value)
    End Sub
    
    Public Property Get Item(key As String) As Variant
        Dim i As Integer
        For i = 1 To m_items.Count
            If m_items(i)(0) = key Then
                Set Item = m_items(i)(1)
                Exit Property
            End If
        Next
        Err.Raise 9, , "键不存在"
    End Property
    
    Public Sub Clear()
        Dim item As Variant
        For Each item In m_items
            Set item(1) = Nothing
        Next
        m_items.Clear
    End Sub
End Class

使用这个自定义类时，Clear或类销毁时会自动释放所有对象引用，不会出现泄漏问题。

原因分析 #

.NET的Hashtable和SortedList在处理COM对象（VBA类属于COM对象）时，内部引用计数管理与VBA垃圾回收机制存在兼容性问题。直接调用Clear仅移除集合条目，但不会主动清空COM对象的引用，导致VBA垃圾回收器无法检测到对象已无引用，无法触发Terminate事件。手动置空引用能强制解除.NET集合的持有，让VBA正常回收对象。

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