Excel VBA Object Not Released as Expected When Using .NET 3.5 Hashtable

61 Views Asked by At

When I call the Add method of an instance of a .NET 3.5 System.Collections.Hashtable to add an instance of a VBA class, I cannot subsequently get the Hashtable to release the instance of the VBA class.

Here is my minimal VBA class (named TestObject):

Option Explicit

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

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

Here is the Module that instantiates the class and then releases the instance of the class. No Hashtable yet...

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

Note I am creating two references to the object and releasing it twice. When the object is instantiated, the Immediate window displays this:

Initialize

Upon executing the last line (i.e. having released both references to the object), I see

Terminate

added to the Immediate windows.

So far, so good...

But when I run the following code, I never see Terminate appear in the Immediate window:

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

This appears to be a memory leak. How do I get the Hashtable to release its reference to the object it is holding?

UPDATE:

As I stated in a comment, the instance of TestObject is also not released when using a SortedList. For me, this turns out to be a bigger problem, as Dictionary is a well behaved substitute for Hashtable, but I can't find a replacement for 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
0

There are 0 best solutions below