User Defined Type vs Class Speed

Question

I have a workbook with two sheets of data that I need to perform operations on. I started off working with the data directly from the sheets but soon found that to be very slow, so changed things to read the sheets into two arrays (in two separate methods called from Workbook_Open).

I had a user defined type created for the data on each sheet, I then found that I was not able to add these to collections or scripting dictionaries, so I transferred them to classes.

So now I have a class called CDealerData with 4 private fields and public properties for each. The issue is that the execution of reading the data into the array is double that of when I was using a type. Is that just how it is or am I doing something wrong.

Class:

Option Explicit

Private pBAC As String
Private pAccountNumber As String
Private pYear As Integer
Private pUnits As Variant


Public Property Get BAC() As String
    BAC = pBAC
End Property
Public Property Let BAC(Value As String)
    pBAC = Value
End Property


Public Property Get AccountNumber() As String
    AccountNumber = pAccountNumber
End Property
Public Property Let AccountNumber(Value As String)
    pAccountNumber = Value
End Property


Public Property Get Year() As String
    Year = pYear
End Property
Public Property Let Year(Value As String)
    pYear = Value
End Property


Public Property Get Units() As String
    Units = pUnits
End Property
Public Property Let Units(Value As String)
    pUnits = Value
End Property
Option Explicit

Private pBAC As String
Private pAccountNumber As String
Private pYear As Integer
Private pUnits As Variant


Public Property Get BAC() As String
    BAC = pBAC
End Property
Public Property Let BAC(Value As String)
    pBAC = Value
End Property


Public Property Get AccountNumber() As String
    AccountNumber = pAccountNumber
End Property
Public Property Let AccountNumber(Value As String)
    pAccountNumber = Value
End Property


Public Property Get Year() As String
    Year = pYear
End Property
Public Property Let Year(Value As String)
    pYear = Value
End Property


Public Property Get Units() As String
    Units = pUnits
End Property
Public Property Let Units(Value As String)
    pUnits = Value
End Property

Module:

Option Explicit

Public NumberOfYears As Integer

Public DealersData() As CDealerData

Public Sub ReadDealerData()
        
    '** Reads the contents of RawData into an Array
    '** of custom type DealerData, defined above
    
    Dim MyDealerData As CDealerData
    Dim LastRow As Long
    Dim i As Long
    Dim j As Long
        
    LastRow = SheetRawData.UsedRange.Rows.Count
    
    ReDim DealersData(LastRow * NumberOfYears)
    
    For i = 0 To LastRow
        For j = 0 To NumberOfYears - 1 'Year columns
            Set MyDealerData = New CDealerData
        
            MyDealerData.BAC = SheetRawData.Cells(i + 2, 1).Value
            MyDealerData.AccountNumber = SheetRawData.Cells(i + 2, 3).Value
            MyDealerData.Year = j + 1
            MyDealerData.Units = CDec(SheetRawData.Cells(i + 2, 4 + j).Value) 'Assign column based on j
            
            Set DealersData(i) = MyDealerData
        Next j
    Next i
    
End Sub

Answer 1

The UDT will be much faster than using a class in this manner for a number of reasons.

1. The UDT is a structure in memory with the data that can be directly written
2. The Class will have Let and Get properties which are functions that execute and have some overhead
3. Creation and Destruction of the class would add to a tiny bit of overhead, but nothing noticeable in your case

To improve performance, you may consider using Public Variables instead of private properties, but then again that may defeat the purpose of you using a class.

• If you are looking to simply use this as a data container, you are better off with a User-defined data type.
• If you wish to further manipulate this data with Class specific functions, then the Class approach is better

---

Also, a general approach to speeding things up is to access the spreadsheet as few times as possible.

For e.g. code such as the following

For i = 1 to 10
   Variable = Worksheets("Sheet1").Range("A1").Cell(i,1).Value
Next i

can be replaced by

Dim VariantArray as Variant
VariantArray = Workeheets("Sheet1").Range("A1:A10")

' Now VariantArray(0,0) has the first element, (1,0) has the second, etc.

---

A note on profiling: Do note @BlackHawk's suggestion in the comments below, to use the MicroTimer tool. It is incredibly useful for isolating portions of code and finding the performance impact to a very precise level.

Also, while this is true for any platform, VBA performance can be inconsistent at times depending on how much pressure is there on Excel's resources at the moment, and hence, even though the MicroTimer is precise, it might not be accurately representative and you might want to consider running loops at different times to correctly gauge the impact of different sections of your code.

Answer 2

As the first I would optimze the CDealerData-Class as follows:

Private pUnits As Decimal 'instead of Variant, the internal mapping uses Time
Private pYear As Long 'instead of integer because outside of the Class you calc with Long

Furthermore I suggest you create a Method to set the Data by one line instead of writeable Properties:

Public Sub SetData(BAC As String, AccountNumber as String, Year as Long, Units as Decimal)
    pBAC = BAC 
    pAccountNumber = AccountNumber
    pYear = Year
    pUnits = Units
End Sub

The usage in your Module would look like this:

For i = 0 To LastRow
    For j = 0 To NumberOfYears - 1 'Year columns
        Set MyDealerData = New CDealerData
        MyDealerData.SetData(SheetRawData.Cells(i + 2, 1).Value, SheetRawData.Cells(i + 2, 3).Value,  j + 1, CDec(SheetRawData.Cells(i + 2, 4 + j).Value))
        'Assign  column based on j

        Set DealersData(i) = MyDealerData
    Next j
Next i

Also with a Class you can use a Collection and you woudn't need ReDim for the Array.

Hope it helps.

Cheers Andy

Answer 3

Use this syntax to read entire arrays with one operation Dim x() as Variant : x = Range("A1").Resize(40,20).Value.

This will read the cells starting from A1 in 40 rows and 20 columns into an 2D array of Variant(,).

The you can loop through this array to put values into the user type and it will be much faster, like DealersData(i*NumberOfYears+j).BAC = x(2*i-1,j) or however you have things organized.