Collection v class for speed

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,395
This is taken from here:

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

This is the data structure:

1598877643641.png



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

Code:
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:

Code:
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
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,670
Office Version
  1. 2010
Platform
  1. Windows
I think you have made a copy and paste error because the two sets of code look identical to me!!
 

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,395
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.

Code:
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.

Code:
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,920
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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,406

ADVERTISEMENT

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...

VBA Code:
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

Well-known Member
Joined
Apr 11, 2013
Messages
1,395
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,920

ADVERTISEMENT

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

Code:
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,168
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,805
Office Version
  1. 2016
  2. 2010
  3. 2007
Platform
  1. Windows
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.
VBA Code:
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

Well-known Member
Joined
Apr 11, 2013
Messages
1,395
Do you use "for next loop" to mean incrementing a counter as in

Code:
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:

Code:
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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,531
Messages
5,602,202
Members
414,513
Latest member
junbuggle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top