User Defined Type vs Class Speed

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

Solution

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

The UDT is a structure in memory with the data that can be directly written
The Class will have Let and Get properties which are functions that execute and have some overhead
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.