Collection slow

[tiredofit]

I have some data (spanning a few hundred thousand rows) in column A, such as:

Day
Monday
Tuesday
Friday

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 = pDayOfWeek
    
End Property
Public Property Let Fruit(ByVal F As String)
    
    pDayOfWeek = F
    
End Property

Standard Module:

Option Explicit
Sub Test()
    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
    
    For Counter = 1 To DataArrayRows - 1
    
        FruitArray(Counter, 1) = MyColl.Item(Counter).Fruit
        
    Next Counter
    
    Sheet1.Cells(2, 2).Resize(DataArrayRows, 1).Value = FruitArray()
    
End Sub

The code above works but seems to take a long time on this line:

For Counter = 1 To DataArrayRows - 1
    
        FruitArray(Counter, 1) = MyColl.Item(Counter).Fruit
        
    Next Counter

Why is that?

Thanks

PS I know I could use a VLookup instead!

 

[StephenCrump]

I experimented as follows:

Dim abc

For Each abc In MyColl
        i = i + 1
        FruitArray(i, 1) = abc.Fruit
    Next abc​

and it still worked, meaning the variable (abc in this case) has nothing to do with classes. It's just a variable to "extract" the elements in the collection, MyColl?

Not quite ...

Consider:

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
    MsgBox "You have a worksheet called " & ws.Name
Next ws

In each iteration of the loop, VBA takes an item from the Collection (in this case a Worksheets Collection). It knows that this item is a Worksheet object, and assigns it to the variable ws. By referring to ws, we can now access all the methods and properties of that Worksheet, e.g. its .Name

With your loop, VBA knows that each item in your Collection is a clsData object, so when the loop assigns it to abc, you can access all the properties (you have two) and methods (you don't have any, but you can write them for your Class) of that object.

Your code will still work if you simply declare Dim abc (or Dim ws in the example above) because the default data type is Variant, which can contain data of any type (with minor exceptions). But it's always better to explicitly declare your data types, e.g. Dim abc as clsData. This way, when you refer to abc, VBA immediately knows it is dealing with a clsData object. If abc is a Variant, think of VBA needing to do a quick inspection of the object every time it picks it up, to see what it might contain this time.

 

[tiredofit]

Not quite ...

Consider:

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
    MsgBox "You have a worksheet called " & ws.Name
Next ws

In each iteration of the loop, VBA takes an item from the Collection (in this case a Worksheets Collection). It knows that this item is a Worksheet object, and assigns it to the variable ws. By referring to ws, we can now access all the methods and properties of that Worksheet, e.g. its .Name

With your loop, VBA knows that each item in your Collection is a clsData object, so when the loop assigns it to abc, you can access all the properties (you have two) and methods (you don't have any, but you can write them for your Class) of that object.

Your code will still work if you simply declare Dim abc (or Dim ws in the example above) because the default data type is Variant, which can contain data of any type (with minor exceptions). But it's always better to explicitly declare your data types, e.g. Dim abc as clsData. This way, when you refer to abc, VBA immediately knows it is dealing with a clsData object. If abc is a Variant, think of VBA needing to do a quick inspection of the object every time it picks it up, to see what it might contain this time.

Thanks again, I totally agree.

One final point, I tested this version (WITHOUT using a collection) and in terms of speed, it's the same as WITH a collection (which surprised me because the version WITH a collection requires two loops).

    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
    
    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
        
        FruitArray(Counter - 1, 1) = MyData.Fruit
        
    Next Counter
   
    Sheet1.Cells(2, 2).Resize(DataArrayRows, 1).Value = FruitArray()

so my question is: is there any point to using a collection (in this example anyway)?

 

[RoryA]

Not really, but then there's no point to the class either in this example.

 

[tiredofit]

Thought the point using the class was so that you can "easily" identify the headings of the data, as opposed to simply referring it to DataArray(1,1), DataArray(1,2), etc.

 

[RoryA]

But you've added all the overhead of a class just so that you can write:

        MyData.DayOfWeek = DataArray(Counter, 1)
        
        Select Case MyData.DayOfWeek

rather than:

       Select Case DataArray(Counter, 1)

in this example. Unless there's a lot more to it, I don't see the point, beyond a learning exercise.

 

[tiredofit]

But you've added all the overhead of a class just so that you can write:

        MyData.DayOfWeek = DataArray(Counter, 1)
        
        Select Case MyData.DayOfWeek

rather than:

       Select Case DataArray(Counter, 1)

in this example. Unless there's a lot more to it, I don't see the point, beyond a learning exercise.

Yes it's for learning, as per this article:

https://excelmacromastery.com/vba-class-modules/

(or have I misunderstood the point in the article)?

 

[RoryA]

No, I'm just saying that it doesn't actually add anything useful to this particular example. However as an exercise in learning to use classes it does, presumably, increase your knowledge.

 

[tiredofit]

No, I'm just saying that it doesn't actually add anything useful to this particular example. However as an exercise in learning to use classes it does, presumably, increase your knowledge.

Phew,

Thought I've been wasting my (and others on the forum) time.