Clearing collection slow

[tiredofit]

Further to this thread I started:

https://www.mrexcel.com/forum/excel-questions/1052067-collection-slow.html

Here's the code:

ClsData:

Option Explicit

    Private pDayOfWeek As String
    Private pFruit As String

Public Property Get DayOfWeek() As String
    
    DayOfWeek = pDayOfWeek
    
End Property

Public Property Let DayOfWeek(ByVal D As String)
    
    pDayOfWeek = D
    
End Property

Public Property Get Fruit() As String
    
   Fruit = pFruit
    
End Property

Public Property Let Fruit(ByVal F As String)
    
    pFruit = F
    
End Property

Standard Module:

    Dim DataArray() As Variant
    
    DataArray = Sheet1.Cells(1, 1).CurrentRegion.Value
    
    Dim DataArrayRows As Long
    
    DataArrayRows = UBound(DataArray(), 1)
    
    Dim FruitArray() As Variant
    
    ReDim FruitArray(1 To DataArrayRows, 1 To 1) As Variant
    
    Dim Counter As Long
    
    Dim MyData As ClsData
    
    Dim MyColl As Collection
    Set MyColl = New Collection
    
    For Counter = 2 To DataArrayRows
            
        Set MyData = New ClsData
        
        MyData.DayOfWeek = DataArray(Counter, 1)
        
        Select Case MyData.DayOfWeek
        
            Case "Monday"
                
                MyData.Fruit = "Orange"
                
            Case "Tuesday"
                
                MyData.Fruit = "Apple"
            
            Case Else
            
                MyData.Fruit = "Banana"
                
        End Select
        
        MyColl.Add MyData
        
    Next Counter
    
    Dim c As ClsData
    Dim i As Long
    
    For Each MyData In MyColl
        i = i + 1
        FruitArray(i, 1) = MyData.Fruit
    Next MyData
    
    Set MyColl = Nothing 'NEW LINE
    
    Sheet1.Cells(2, 2).Resize(DataArrayRows, 1).Value = FruitArray()

This is the new line I added:

Set MyColl = Nothing​

and it hangs around that line for a very long time. (There are 300,000 values in column A).

Is that expected or should clearing collections be fast?

If it's expected, should I not bother clearing collections, classes, arrays after using them because they'd clear automatically once outside the Sub?

Thanks

 

[mole999]

application.enableevents = false
range("a2:A30000").clearcontents
application.enableevents = true

 

[thisoldman]

The time to clear a collection depends on the size of the Collection and the type of items in the Collection. You have a 300,000 item collection of class objects, each class object with four properties. On my PC, it takes 12.5–14 seconds to set MyColl to Nothing. When I instead create a collection of MyData.fruit items, it takes 0.03125 seconds to run `Set MyColl = Nothing`.

This is what I tested:

Sub TestIt()
    Dim i As Long
    For i = 1 To 5
        TryIt
    Next
End Sub
''''

Sub TryIt()
    Dim DataArray() As Variant
    Dim DataArrayRows As Long
    Dim FruitArray() As Variant
    Dim Counter As Long
    Dim MyData As clsData
    Dim MyColl As Collection
    Dim i As Long
    Dim start As Single

    DataArray = Sheet1.Cells(1, 1).CurrentRegion.Value
    DataArrayRows = UBound(DataArray(), 1)
    ReDim FruitArray(1 To DataArrayRows, 1 To 1) As Variant

    Set MyColl = New Collection

    For Counter = 2 To DataArrayRows
        Set MyData = New clsData
        MyData.DayOfWeek = DataArray(Counter, 1)

        Select Case MyData.DayOfWeek
            Case "Monday"
                MyData.fruit = "Orange"
            Case "Tuesday"
                MyData.fruit = "Apple"
            Case Else
                MyData.fruit = "Banana"
        End Select

        ' MyColl.Add MyData              ' Original
        MyColl.Add MyData.fruit        ' New
    Next Counter

' Original loop
'    For Each MyData In MyColl
'        i = i + 1
'        FruitArray(i, 1) = MyData.fruit
'    Next MyData

' New loop
    Dim MyFruit As Variant
    For Each MyFruit In MyColl
        i = i + 1
        FruitArray(i, 1) = MyFruit
    Next MyFruit

    start = Timer
    Set MyColl = Nothing
    Debug.Print "MyColl = Nothing: " & CStr(Timer - start) & " seconds"

    Application.ScreenUpdating = False
    Sheet1.Cells(2, 2).Resize(DataArrayRows, 1).Value = FruitArray()
    Application.ScreenUpdating = True
End Sub
''''

 

[tiredofit]

The time to clear a collection depends on the size of the Collection and the type of items in the Collection. You have a 300,000 item collection of class objects, each class object with four properties. On my PC, it takes 12.5–14 seconds to set MyColl to Nothing. When I instead create a collection of MyData.fruit items, it takes 0.03125 seconds to run `Set MyColl = Nothing`.

This is what I tested:

Sub TestIt()
    Dim i As Long
    For i = 1 To 5
        TryIt
    Next
End Sub
''''

Sub TryIt()
    Dim DataArray() As Variant
    Dim DataArrayRows As Long
    Dim FruitArray() As Variant
    Dim Counter As Long
    Dim MyData As clsData
    Dim MyColl As Collection
    Dim i As Long
    Dim start As Single

    DataArray = Sheet1.Cells(1, 1).CurrentRegion.Value
    DataArrayRows = UBound(DataArray(), 1)
    ReDim FruitArray(1 To DataArrayRows, 1 To 1) As Variant

    Set MyColl = New Collection

    For Counter = 2 To DataArrayRows
        Set MyData = New clsData
        MyData.DayOfWeek = DataArray(Counter, 1)

        Select Case MyData.DayOfWeek
            Case "Monday"
                MyData.fruit = "Orange"
            Case "Tuesday"
                MyData.fruit = "Apple"
            Case Else
                MyData.fruit = "Banana"
        End Select

        ' MyColl.Add MyData              ' Original
        MyColl.Add MyData.fruit        ' New
    Next Counter

' Original loop
'    For Each MyData In MyColl
'        i = i + 1
'        FruitArray(i, 1) = MyData.fruit
'    Next MyData

' New loop
    Dim MyFruit As Variant
    For Each MyFruit In MyColl
        i = i + 1
        FruitArray(i, 1) = MyFruit
    Next MyFruit

    start = Timer
    Set MyColl = Nothing
    Debug.Print "MyColl = Nothing: " & CStr(Timer - start) & " seconds"

    Application.ScreenUpdating = False
    Sheet1.Cells(2, 2).Resize(DataArrayRows, 1).Value = FruitArray()
    Application.ScreenUpdating = True
End Sub
''''

Thanks for your example.

Are you saying if the collection contained more than just Fruit, eg colour or size, etc. and I wanted to clear the collection, it MUST take longer?

 

[tiredofit]

The time to clear a collection depends on the size of the Collection and the type of items in the Collection. You have a 300,000 item collection of class objects, each class object with four properties. On my PC, it takes 12.5–14 seconds to set MyColl to Nothing. When I instead create a collection of MyData.fruit items, it takes 0.03125 seconds to run `Set MyColl = Nothing`.

This is what I tested:

Sub TestIt()
    Dim i As Long
    For i = 1 To 5
        TryIt
    Next
End Sub
''''

Sub TryIt()
    Dim DataArray() As Variant
    Dim DataArrayRows As Long
    Dim FruitArray() As Variant
    Dim Counter As Long
    Dim MyData As clsData
    Dim MyColl As Collection
    Dim i As Long
    Dim start As Single

    DataArray = Sheet1.Cells(1, 1).CurrentRegion.Value
    DataArrayRows = UBound(DataArray(), 1)
    ReDim FruitArray(1 To DataArrayRows, 1 To 1) As Variant

    Set MyColl = New Collection

    For Counter = 2 To DataArrayRows
        Set MyData = New clsData
        MyData.DayOfWeek = DataArray(Counter, 1)

        Select Case MyData.DayOfWeek
            Case "Monday"
                MyData.fruit = "Orange"
            Case "Tuesday"
                MyData.fruit = "Apple"
            Case Else
                MyData.fruit = "Banana"
        End Select

        ' MyColl.Add MyData              ' Original
        MyColl.Add MyData.fruit        ' New
    Next Counter

' Original loop
'    For Each MyData In MyColl
'        i = i + 1
'        FruitArray(i, 1) = MyData.fruit
'    Next MyData

' New loop
    Dim MyFruit As Variant
    For Each MyFruit In MyColl
        i = i + 1
        FruitArray(i, 1) = MyFruit
    Next MyFruit

    start = Timer
    Set MyColl = Nothing
    Debug.Print "MyColl = Nothing: " & CStr(Timer - start) & " seconds"

    Application.ScreenUpdating = False
    Sheet1.Cells(2, 2).Resize(DataArrayRows, 1).Value = FruitArray()
    Application.ScreenUpdating = True
End Sub
''''

It turned out when I added Application.ScreenUpdating = False, it ran a lot quicker, including clearing the collection.

I am puzzled as I'm not pasting any values onto the worksheet .

 

[mole999]

screenupdating refreshes your view, if it can get away without screen writes then the processing can be applied to the code

 

[thisoldman]

I ran the test on the original and on the reworked subs today, after a cold boot of the machine. The time for the original sub to delete the collection changed from 12 to 14 seconds yesterday, to 0.2 seconds today. I was also running the subs on a recovered file—not an original, intentional save. I've never seen such a stark difference in run times, one day to the next.

 

[tiredofit]

So Excel / VBA is, at best, inconsistent, perhaps even unreliable?

 

[thisoldman]

So Excel / VBA is, at best, inconsistent, perhaps even unreliable?

The short answer is its performance can be erratic. VBA is an old language. Microsoft, to their credit, keeps Excel backwardly compatible, mostly, and VBA and the VBEditor and the compiler have gathered some cruft over the years.

Code sometimes performs differently when the VBEditor is open compared to when the editor is closed. The most annoying things I have experienced have usually occurred after a long session with the VBEditor open and after many compilations. Code that was performing quickly takes forever to run or Excel freezes up and crashes. Often, closing Excel completely and reopening it removes the slowdown. But, on a few occasions, I have had to export and remove modules, then reimport them to restore performance.

I should point out that I'm often a curmudgeon. VBA is among my least favorite scripting languages—the syntax doesn't flow for me. Plus I dislike PascalCase.