Memory not released when using Dictionarys in 64-bit Excel

Question

When I store data in a dictionary, the memory is not released after it goes out of scope or is set to Nothing. I have to explicitly use the RemoveAll method to release the memory.

This problem occurs exclusively with Office 365 64-bit and not with the previously used 32-bit version.

For Example:

Public Sub testDict()
    Dim dict As Dictionary
    Dim i As Long
    
    Set dict = New Dictionary
    
    For i = 1 To 100000
        dict.Add i, Format(i, "0000000000")
    Next
    
    dict.RemoveAll
    Set dict = Nothing
End Sub

If dict.RemoveAll is not used in this example, the memory will not be released after the Sub is finished. Every new execution of this Sub will use new memory.

Releasing the memory with the RemoveAll method will not work for nested dictionarys.

For Example:

Public Sub testNestedDict()
    Dim i As Long
    Dim j As Long
    Dim k As Long
    Dim dict As Dictionary
    
    Set dict = New Dictionary
    
    For i = 1 To 10
        dict.Add i, New Dictionary
    
        For j = 1 To 10
            dict(i).Add j, New Dictionary
        
            For k = 1 To 10
                dict(i)(j).Add k, New Dictionary
            Next
        Next
    Next
    
    dict.RemoveAll
    Set dict = Nothing
End Sub

In this case every execution will eat up about ~10MB of memory. I even tried to build a function which erases the dictionary recursively.

Public Sub testNestedDict()
    Dim i As Long
    Dim j As Long
    Dim k As Long
    Dim dict As Dictionary
    
    Set dict = New Dictionary
    
    For i = 1 To 10
        dict.Add i, New Dictionary
    
        For j = 1 To 10
            dict(i).Add j, New Dictionary
        
            For k = 1 To 10
                dict(i)(j).Add k, New Dictionary
            Next
        Next
    Next
    
    dictErase dict
End Sub

Public Function dictErase(ByRef dict As Dictionary)
    Dim key As Variant
    
    If dict Is Nothing Then
        Exit Function
    End If
    
    For Each key In dict.Keys()
        If TypeOf dict(key) Is Dictionary Then
            dictErase dict(key)
        End If
    Next
    
    dict.RemoveAll
    Set dict = Nothing
End Function

This also did not work.

It seems that this issue isn’t confined to dictionaries alone. In the 64-bit version of VBA, memory for out-of-scope variables doesn’t appear to be released properly, or at least behaves differently compared to the 32-bit version.

Not working version: Microsoft® Excel® für Microsoft 365 MSO (Version 2408 Build 16.0.17928.20114) 64 Bit

Working version: Microsoft® Excel® 2019 MSO (16.0.10413.20020) 32-Bit

Answer 1

I have recently been running into some memory issues myself not related to Dictionaries. I've also had to set Application.ScreenUpdating = True at time (this has been do automatically for as long as I remember). Hopefully, there will be a patch coming out soon.

Version: 16.0
Build: 18025
Architecture: 64-bit

Consider using MZ Tools Clean Project feature when you encounter weird behaviors.

MZ Tools Documents

---

I add this per Cristian Buse's comment

Sub AddRegistryEntry()
    Dim RegPath As String
    RegPath = "HKEY_CURRENT_USER\Software\Microsoft\Office\" & Application.Version & "\Excel\Options\"
    
    With CreateObject("WScript.Shell")
        On Error Resume Next
        .RegWrite RegPath & "ForceVBALoadFromSource", 1, "REG_DWORD"
        If Err.Number = 0 Then
            Debug.Print "Successfully Updated"; "; RegPath"
        Else
            Debug.Print "Update Failed"; "; RegPath"
        End If
        On Error GoTo 0
    End With
End Sub