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

 

[mikerickson]

I meant that we don't know the lower index.
Some arrays are 0 based (like what you get from Split), some are 1 based (like what you get from Range.Value), Some can vary (like what you get from Array (depending on the Option Base setting chosen). And the coder can set any LBound that they want, even a negative one).

The LBound and the UBound of an array are not fixed and things (like my generic QuickSortAnArray sub) have to take that into account unless they are carefully optimized for that specific situation.

With Collections, one knows that the LBound is always 1.

 

[tiredofit]

I meant that we don't know the lower index.
Some arrays are 0 based (like what you get from Split), some are 1 based (like what you get from Range.Value), Some can vary (like what you get from Array (depending on the Option Base setting chosen). And the coder can set any LBound that they want, even a negative one).

The LBound and the UBound of an array are not fixed and things (like my generic QuickSortAnArray sub) have to take that into account unless they are carefully optimized for that specific situation.

With Collections, one knows that the LBound is always 1.

Thanks for clarifying.