Speed test

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,825
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Can someone please test a program for its speed?

According to this article:

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

if there were 50,000 rows of data, it claims to take only 3.55 seconds to run.

I tried it and it took a lot longer.

This is in a class called houseobject:

Code:
Public houseType As String
Public price As Double
Public numberOfRooms As Integer
Public location As StringFunction getType() As StringSelect Case houseType
   Case "Flat", "Appartment"
getType = "Type A"
Else
getType = "Type B"
End Select
End Function

this is in a standard module:

Code:
Option Explicit

Sub Macro1()

Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet: Set ws = wb.Sheets("Data")
Dim dataRange As Range: Set dataRange = ws.Range("A2:D50001")
Dim outputRange As Range: Set outputRange = ws.Range("F2")
Dim dataCollection As New Collection
Dim i As Long
Dim count As Integer
Dim ho As houseobject
'Read data and create object
For i = 1 To dataRange.Rows.count
   Set ho = New houseobject
   ho.houseType = CStr(dataRange(i, 1))
   ho.price = CDbl(dataRange(i, 2))
   ho.numberOfRooms = CInt(dataRange(i, 3))
   ho.location = CStr(dataRange(i, 4))
'check logic
   If ho.getType() = "Type A" And ho.price > 100000 Then
      dataCollection.Add ho
   End If
Next i
'output
For Each ho In dataCollection
   outputRange.Offset(count, 0) = ho.houseType
   outputRange.Offset(count, 1) = ho.price
   outputRange.Offset(count, 2) = ho.numberOfRooms
   outputRange.Offset(count, 3) = ho.location
   count = count + 1
Next ho
End Sub

the data looks like this, (cell A1 contains the value "House", A2 contains "Price", etc.) so copy and paste just the data down to row 50001.

data.png


Thanks
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
you could speed up your macro by a factor of 1000 or so by using variant arrays, to change the input to variant array only requires two lines to change :
VBA Code:
'Dim dataRange As Range: Set dataRange = ws.Range("A2:D50001")
dim datarange as variant
dataRange = ws.Range("A2:D50001")
' and
'
'For i = 1 To dataRange.Rows.count
For i = 1 To UBound(dataRange, 1)
Changing the output array is a bit more work because you have used offset which doesn't work with arrays, but you will get just as much improvement in time. I will leave you to do that one.
You probably won't need to test the speed , because it will only take seconds
 
Upvote 0
you could speed up your macro by a factor of 1000 or so by using variant arrays, to change the input to variant array only requires two lines to change :
VBA Code:
'Dim dataRange As Range: Set dataRange = ws.Range("A2:D50001")
dim datarange as variant
dataRange = ws.Range("A2:D50001")
' and
'
'For i = 1 To dataRange.Rows.count
For i = 1 To UBound(dataRange, 1)
Changing the output array is a bit more work because you have used offset which doesn't work with arrays, but you will get just as much improvement in time. I will leave you to do that one.
You probably won't need to test the speed , because it will only take seconds

Thanks, I tried your suggestion amending the two lines of code on 50,000 rows of data but it still took a long time.

It's just that the article claims it takes only 3.55 seconds using classes but I couldn't replicate it.

My approach via arrays would be the following:

Code:
Option Explicit

Sub Fast()

    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 OutputArray() As Variant
 
    ReDim OutputArray(1 To DataArrayRows, 1 To DataArrayCols) As Variant
 
    Dim i As Long
 
    Dim j As Long
 
    Dim k As Integer
 
    j = 1
 
    For i = 2 To DataArrayRows
 
        If DataArray(i, 1) = "Flat" Or DataArray(i, 1) = "Apartment" Then
 
            If DataArray(i, 2) > 10000 Then
 
                For k = 1 To DataArrayCols
             
                    OutputArray(j, k) = DataArray(i, k)
              
                Next k
             
               j = j + 1
             
            End If
         
        End If
 
    Next i
 
    Sheet1.Cells(2, 11).Resize(j, 4).Value = OutputArray()
 
End Sub

and this is very fast.
 
Upvote 0
That is exactly how I would do it, every time you access the worksheet takes a long time in vba , so doing it in a loop is always going to be slow, so it doesn't matter how fast creating classes is , the code will be slow. I looked at the article you posted and I can't see how that code could possibly do 50k rows in 3.5 seconds. So I don't believe it.
Classes are good some things, but only when it helps with understanding or simplifying the code, or for reusability reasons.
 
Upvote 0
That is exactly how I would do it, every time you access the worksheet takes a long time in vba , so doing it in a loop is always going to be slow, so it doesn't matter how fast creating classes is , the code will be slow. I looked at the article you posted and I can't see how that code could possibly do 50k rows in 3.5 seconds. So I don't believe it.
Classes are good some things, but only when it helps with understanding or simplifying the code, or for reusability reasons.

Thanks for checking the claim.

In my opinion, learning to program VBA in oo fashion is tough because:

1. You don't have! Procedural works and it's what most power Excel programmers choose to do.
2. Books introduce classes but leave you there. It doesn't explain how you should plan BEFORE programming.
3. People who do write VBA in oo probably had prior exposure in languages such as C#.

My main motivation for learning oo in VBA is on the off chance that I need to maintain code written by others. Otherwise, I wouldn't bother.

How did you learn oo in VBA?
 
Upvote 0
I didn't, I have been programming as part of my job for nearly 50 years, so i got exposed to oo years ago and used it with various languages. I do agree with you vba is not set up to make it easy, and it using the default excel objects can cause problems. E.g. using ranges in loops
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,101
Members
448,548
Latest member
harryls

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
Back
Top