tiredofit
Well-known Member
- Joined
- Apr 11, 2013
- Messages
- 1,825
- Office Version
- 365
- 2019
- Platform
- Windows
Can someone please test a program for its speed?
According to this article:
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:
this is in a standard module:
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.
Thanks
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.
Thanks