I am using collections in my VBA code to store objects (which also contain collections inside), and I am having issues with the time it takes to clear the collection (there are around 10,000 records, and the tendency is for this number to increase). Both the Set myCollection = New Collection and Set myCollection = Nothing techniques are taking too long to clear the collection. If I don't clear it, my code usually freezes at the end while performing the cleanup in the background.

Is there a fast method to clear collections in VBA? How can I avoid this issue?

Note: The item-by-item removal technique (collection.Remove(i)) is also taking too long.

I tryed to use all the main solutions that I saw on internet: Set myCollection = New Collection Set myCollection = Nothing collection.Remove(i)

I am using collections in my VBA code to store objects (which also contain collections inside), and I am having issues with the time it takes to clear the collection (there are around 10,000 records, and the tendency is for this number to increase). Both the Set myCollection = New Collection and Set myCollection = Nothing techniques are taking too long to clear the collection. If I don't clear it, my code usually freezes at the end while performing the cleanup in the background.

Is there a fast method to clear collections in VBA? How can I avoid this issue?

Note: The item-by-item removal technique (collection.Remove(i)) is also taking too long.

I tryed to use all the main solutions that I saw on internet: Set myCollection = New Collection Set myCollection = Nothing collection.Remove(i)

• excel
• vba
• memory
• memory-management
• collections

• 1 Are you wanting to only clear the collection references to the specified objects? Or are you open to clearing those references AND the actual objects? – Infrequent Coder Commented Mar 17 at 4:59
• 1 Could you share the complete (relevant) code you're using and more detail about the data? Have you considered using other data structures ((jagged) arrays, dictionaries)? – VBasic2008 Commented Mar 17 at 8:44

1 Answer 1

The reason for this slow behavior is not clearing the collection but the fact that all the objects need to be destroyed.

Objects in VBA are destroyed whenever the last reference to them is removed. You can easily prove this by creating 2 collections and add the objects to both of them: Clearing the first collection will run instantly (method doesn't play a role) because none of the objects are destroyed.

Sub testSub()
    Const maxEntries = 100000
    
    ' Create a Collection containing objects
    Dim c1 As New Collection, c2 As New Collection
    Dim i As Long
    For i = 1 To maxEntries
        ' Create dummy objects
        Dim o As Class1
        Set o = New Class1
        o.s = "Test" & i
        o.v = i * 1.23
        
        c1.Add o, CStr(i)
        c2.Add o, CStr(i)
    Next
        
    Debug.Print "Start to clear " & Now
    Set c1 = New Collection
    Debug.Print "First collection cleared " & Now
    Set c2 = New Collection
    Debug.Print "Second collection cleared " & Now
    Debug.Print "Done " & Now
End Sub

Start to clear 17.03.2025 09:32:03
First collection cleared 17.03.2025 09:32:03
Second collection cleared 17.03.2025 09:32:08
Done 17.03.2025 09:32:08

You can see that clearing the second collection took 5s while the first was cleared instantly. If you don't clear the collection, the same waiting time appears when the code finishes.

I don't think that you can to anything against that. You could mitigate the effect by removing the elements one by one and add some DoEvent-statement. With that, the user can already continue to do something on Excel. However, you need to be careful that the work of the user doesn't interfere with your macro running. Note that DoEvent comes with a price tag, so don't execute it for every iteration

Do While c2.Count > 0
    c2.Remove 1
    If c2.Count Mod 100 = 0 Then DoEvents
Loop

Note that using a dictionary or array instead of a collection will not change the runtime as the long runtime is caused by destroying the single objects