User Defined Type vs Class Speed

class, excel, user-defined-types, vba

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.

Problem

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

Original source