Collection v class for speed

[tiredofit]

This is taken from here:

https://medium.com/@phunt6056/vba-and-oop-a95f9bcacaa6

This is the data structure:

I have adapted the code as follows, using combination of arrays and classes.

What I don't understand is why is Method 2 (accessing the class) faster than Method 1 (accessing the collection)?

Method 1

Option Explicit

Sub UsingOOAndArrays()

    Dim DataArray() As Variant
   
    DataArray() = Sheet1.Cells(1, 1).CurrentRegion.Value
   
    Dim DataArrayRows As Long
   
    DataArrayRows = UBound(DataArray(), 1)
   
    Dim DataArrayCols As Long
   
    DataArrayCols = UBound(DataArray(), 2)
   
    Dim i As Long
   
    Dim House As houseobject
   
    Dim Coll As Collection
    Set Coll = New Collection
   
    For i = 2 To DataArrayRows
   
        Set House = New houseobject
       
        House.houseType = DataArray(i, 1)
        House.price = DataArray(i, 2)
        House.numberOfRooms = DataArray(i, 3)
        House.location = DataArray(i, 4)
       
        If House.getType = "Type A" And House.price > 100000 Then
           
            Coll.Add Item:=House
           
        End If
   
    Next i
   
    Dim Counter As Long
   
    Counter = Coll.Count
   
    Dim OutputArray() As Variant
   
    ReDim OutputArray(1 To Counter, 1 To DataArrayCols) As Variant
   
    Counter = 1


'***************
   
    For Each House In Coll
   
        OutputArray(Counter, 1) = Coll.Item(Counter).houseType
        OutputArray(Counter, 2) = Coll.Item(Counter).location
        OutputArray(Counter, 3) = Coll.Item(Counter).numberOfRooms
        OutputArray(Counter, 4) = Coll.Item(Counter).price
   
        Counter = Counter + 1
       
    Next House
   
'***************
   
    Sheet1.Cells(2, 16).Resize(Counter, DataArrayCols).Value = OutputArray()
   
End Sub

Method 2:

Option Explicit

Sub UsingOOAndArrays()

    Dim DataArray() As Variant
   
    DataArray() = Sheet1.Cells(1, 1).CurrentRegion.Value
   
    Dim DataArrayRows As Long
   
    DataArrayRows = UBound(DataArray(), 1)
   
    Dim DataArrayCols As Long
   
    DataArrayCols = UBound(DataArray(), 2)
   
    Dim i As Long
   
    Dim House As houseobject
   
    Dim Coll As Collection
    Set Coll = New Collection
   
    For i = 2 To DataArrayRows
   
        Set House = New houseobject
       
        House.houseType = DataArray(i, 1)
        House.price = DataArray(i, 2)
        House.numberOfRooms = DataArray(i, 3)
        House.location = DataArray(i, 4)
       
        If House.getType = "Type A" And House.price > 100000 Then
           
            Coll.Add Item:=House
           
        End If
   
    Next i
   
    Dim Counter As Long
   
    Counter = Coll.Count
   
    Dim OutputArray() As Variant
   
    ReDim OutputArray(1 To Counter, 1 To DataArrayCols) As Variant
   
    Counter = 1


'***************
   
    For Each House In Coll

        OutputArray(Counter, 1) = House.houseType
        OutputArray(Counter, 2) = House.location
        OutputArray(Counter, 3) = House.numberOfRooms
        OutputArray(Counter, 4) = House.price

        Counter = Counter + 1

    Next House
   
'***************

   
    Sheet1.Cells(2, 16).Resize(Counter, DataArrayCols).Value = OutputArray()
   
End Sub

Thanks

 

[offthelip]

I think you have made a copy and paste error because the two sets of code look identical to me!!

 

[tiredofit]

I think you have made a copy and paste error because the two sets of code look identical to me!!

No, they're similar though not identical.

The slow method.

For Each House In Coll
   
        OutputArray(Counter, 1) = Coll.Item(Counter).houseType
        OutputArray(Counter, 2) = Coll.Item(Counter).location
        OutputArray(Counter, 3) = Coll.Item(Counter).numberOfRooms
        OutputArray(Counter, 4) = Coll.Item(Counter).price
   
        Counter = Counter + 1
       
    Next House

The fast method.

For Each House In Coll

        OutputArray(Counter, 1) = House.houseType
        OutputArray(Counter, 2) = House.location
        OutputArray(Counter, 3) = House.numberOfRooms
        OutputArray(Counter, 4) = House.price

        Counter = Counter + 1

    Next House

 

[mikerickson]

I would explain the difference in speed by In each case you are looping through the collection, an in both cases you have House "in hand" every loop.

The first code then has to get the value of Counter, get the Item property, and retrieve coll.Item(counter) four times, while the second loop does none of that

 

[Domenic]

While Mike has provided you with an explanation, here's an amendment to your code that uses the With/End With statement to improve efficiency...

For Counter = 1 To Coll.Count
  
   With Coll.Item(Counter)
        OutputArray(Counter, 1) = .houseType
        OutputArray(Counter, 2) = .Location
        OutputArray(Counter, 3) = .numberOfRooms
        OutputArray(Counter, 4) = .Price
    End With
      
Next Counter

 

[tiredofit]

Thanks for the explanation.

As a rule, I read the following optimal solutions:

Collections - Use For Each Loop
Arrays - Use For Next
Dictionaries - No idea!

 

[mikerickson]

Do you use "for next loop" to mean incrementing a counter as in

For i = 1 to 5
     Debug.Print myArray(i)
next i

I don't think that that is the optimal way to loop through and array. For Each is particulaly suited for arrays, because, of those three objects, arrays are the only thing that we don't know the lower index.

 

[RoryA]

In my experience, For Each is fractionally slower with very large arrays, but it's negligible unless you need to do it repeatedly and absolutely have to keep runtime to the bare minimum.

 

[ZVI]

For-Each is much faster vs For-Next for a Collection.
Actually, the Item(Index) of Collection is very slow function - run this code to see the difference.

Sub SpeedTest()

  Dim i As Long, j As Long, t As Single
  Dim s As String, v As Variant
  Dim Coll As New Collection
  
  j = 20000 ' increase j to see more difference
  For i = 1 To j
    Coll.Add i, CStr(i)
  Next
  
  t = Timer
  For Each v In Coll
    s = v
  Next
  Debug.Print "For-Each", Round(Timer - t, 3) & "s"
  
  t = Timer
  With Coll
    For i = 1 To j
      s = .Item(i)
    Next
  End With
  Debug.Print "For-Next", Round(Timer - t, 3) & "s"
  
End Sub

 

[tiredofit]

Do you use "for next loop" to mean incrementing a counter as in

For i = 1 to 5
     Debug.Print myArray(i)
next i

I don't think that that is the optimal way to loop through and array. For Each is particulaly suited for arrays, because, of those three objects, arrays are the only thing that we don't know the lower index.

Yes I do but remember, I'm no expert!

Not sure what you mean by "we don't know the lower index"

Do you mean you don't know the ubound?

For example:

Dim DataArray() As Variant
DataArray()=Range("A1").CurrentRegion.Value ' Read the data
Dim DataArrayRows As Integer
DataArrayRows=Ubound(DataArray(), 1)

Dim ResultsArray() As Variant
ReDim ResultsArray(1 to DataArrayRows, 1 To 1) As Variant ' We know the dimension of the ResultsArray

Dim Counter As Integer

For Counter = 1 to DataArrayRows

    ' do something like this

    ResultsArray(Counter, 1) = DataArray(Counter, 1) / DataArray(Counter, 2)

Next Counter

Here I am using a For Next Loop on an array.