Are VBA Class module properties slow compared to accessing fields of User Defined Types?

Question

I've been trying to develop a macro with a class module, but get/let seems to take a really long time when compared to UDT's. I'm really interested in why this is, can anyone explain this? I've only found discussions that talk about function/sub execution, which seems to be just as fast.

The current problem is setting a property, which takes about 3000ms for the class (for two million lets) and 120ms for doing the same using a UDT.

I'm trying to decide whether or not I should advise the macro developers to avoid using class modules when they need to get or set a lot of properties. Using only this as data I should, but maybe you have different insights.

I would like to understand why this is so slow. Maybe I'm just doing something wrong.

The example code:

Public Type Participant
    Name As String
    Gender As Integer
End Type

Public Declare Function GetTickCount Lib "kernel32.dll" () As Long

Sub TimeUDT()
   Dim i As Long
   Dim startMs As Long
   startMs = GetTickCount
   Dim participants(1 To 1000000) As Participant
   For i = 1 To 1000000
      participants(i).Name = "TestName"
      participants(i).Gender = 1
   Next
   Debug.Print GetTickCount - startMs
End Sub

Sub TimeCls()
   Dim i As Long
   Dim startMs As Long
   Dim participants(1 To 1000000) As New clsParticipant
   startMs = GetTickCount
   For i = 1 To 1000000
      participants(i).Name = "TestName"
      participants(i).Gender = 1
   Next
   Debug.Print GetTickCount - startMs
End Sub

And the class module (named clsParticipant):

Private iGender As Integer
Private sName As String

Public Property Let Gender(value As Integer)
   iGender = value
End Property
Public Property Get Gender() As Integer
   Gender = iGender
End Property

Public Property Get Name() As String
   Name = sName
End Property
Public Property Let Name(value As String)
   sName = value
End Property

Answer 1

A Webb in his answers correctly highlights the fact that in your test you didn't consider the time required for the creation of the objects, but forgets to add the destruction of the objects.

The time in the code below considers both creation and destruction time, and shows that nothing comes for free, that is it doesn't matter how you do it, at the end the total time is about the same.

I also added the last 3 functions that explicitly destroy the objects in three different ways instead of leaving it to the garbage collector, and the time remains the same.

I added these three test, because I was expecting to see huge changes. I remember of a test I made ages ago where the garbage collector would take 10 or 100 times longer only changing the order of destruction. I wasn't able to reproduce the problem here, perhaps because there aren't as many objects or because the objects are simpler. But I added the code just to show another test you should do if you notice a sudden increase of the time required for the destruction.

Here is my code:

Sub Test()
  Dim T0 As Single

  T0 = timer
  TimeCls_a
  Debug.Print , timer - T0

  T0 = timer
  TimeCls_b
  Debug.Print , timer - T0

  T0 = timer
  TimeCls_c
  Debug.Print , timer - T0

  T0 = timer
  TimeCls_c_up
  Debug.Print , timer - T0

  T0 = timer
  TimeCls_c_dn
  Debug.Print , timer - T0

  T0 = timer
  TimeCls_c_all
  Debug.Print , timer - T0
End Sub

Sub TimeCls_a()
   Dim i As Long
   Dim T0 As Single
   Dim participants(1 To NCYCLES) As New clsParticipant

   T0 = timer
   For i = 1 To NCYCLES
      participants(i).Name = "TestName"
      participants(i).Gender = 1
   Next
   Debug.Print "TimeCls_a:", timer - T0
End Sub

Sub TimeCls_b()
   Dim i As Long
   Dim T0 As Single
   Dim participants(1 To NCYCLES) As clsParticipant
   For i = 1 To NCYCLES
       Set participants(i) = New clsParticipant
   Next i

   T0 = timer
   For i = 1 To NCYCLES
      participants(i).Name = "TestName"
      participants(i).Gender = 1
   Next
   Debug.Print "TimeCls_b:", timer - T0
End Sub

Sub TimeCls_c()
   Dim i As Long
   Dim T0 As Single
   Dim participants(1 To NCYCLES) As clsParticipant
   For i = 1 To NCYCLES
       Set participants(i) = New clsParticipant
   Next i

   T0 = timer
   For i = 1 To NCYCLES
      participants(i).Name = "TestName"
      participants(i).Gender = 1
   Next
   Debug.Print "TimeCls_c:", timer - T0
End Sub

Sub TimeCls_c_up()
   Dim i As Long
   Dim T0 As Single
   Dim participants(1 To NCYCLES) As clsParticipant
   For i = 1 To NCYCLES
       Set participants(i) = New clsParticipant
   Next i

   T0 = timer
   For i = 1 To NCYCLES
      participants(i).Name = "TestName"
      participants(i).Gender = 1
   Next

   For i = 1 To NCYCLES
       Set participants(i) = Nothing
   Next i
   Debug.Print "TimeCls_c_up:", timer - T0
End Sub

Sub TimeCls_c_dn()
   Dim i As Long
   Dim T0 As Single
   Dim participants(1 To NCYCLES) As clsParticipant
   For i = 1 To NCYCLES
       Set participants(i) = New clsParticipant
   Next i

   T0 = timer
   For i = 1 To NCYCLES
      participants(i).Name = "TestName"
      participants(i).Gender = 1
   Next

   For i = NCYCLES To 1 Step -1
       Set participants(i) = Nothing
   Next i
   Debug.Print "TimeCls_c_dn:", timer - T0
End Sub

Sub TimeCls_c_al()
   Dim i As Long
   Dim T0 As Single
   Dim participants() As clsParticipant
   ReDim participants(1 To NCYCLES)
   For i = 1 To NCYCLES
       Set participants(i) = New clsParticipant
   Next i

   T0 = timer
   For i = 1 To NCYCLES
      participants(i).Name = "TestName"
      participants(i).Gender = 1
   Next

   ReDim participants(1 To NCYCLES)
   Debug.Print "TimeCls_c_al:", timer - T0
End Sub

Answer 2

First, I highly recommend using a high-resolution timer so you don't have to test as many iterations. See CTimer using QueryPerformanceCounter.

Here's your baseline on my machine, 10K iterations, high precision timer

Sub TimeUDT()
   Dim i As Long
   Dim timer As New CTimer
   timer.StartCounter
   Dim participants(1 To 10000) As Participant
   For i = 1 To 10000
      participants(i).Name = "TestName"
      participants(i).Gender = 1
   Next
   Debug.Print "Elapsed time: " & timer.TimeElapsed & " ms"
End Sub

Elapsed time: 1.14359022404999 ms

Now believe it or not you actually are taking the hit of object creation inside your loop. Explicitly create them in a loop before starting your timer and see the difference:

Before

Sub TimeCls()
   Dim i As Long
   Dim timer As New CTimer
   Dim participants(1 To 10000) As New clsParticipant

   timer.StartCounter
   For i = 1 To 10000
      participants(i).Name = "TestName"
      participants(i).Gender = 1
   Next
   Debug.Print "Elapsed time: " & timer.TimeElapsed & " ms"
End Sub

Elapsed time: 24.9600996727434 ms

After

Sub TimeCls()
   Dim i As Long
   Dim timer As New CTimer
   'Dim participants(1 To 10000) As New clsParticipant
   Dim participants(1 To 10000) As clsParticipant
   For i = 1 To 10000
       Set participants(i) = New clsParticipant
   Next i

   timer.StartCounter
   For i = 1 To 10000
      participants(i).Name = "TestName"
      participants(i).Gender = 1
   Next
   Debug.Print "Elapsed time: " & timer.TimeElapsed & " ms"
End Sub

Elapsed time: 4.66722880515984 ms

This is only 4x slower than the baseline (after the object creation hit now excluded from the measurement). If you further declare your iGender and sName public and mutate them directly, then the performance even closer to baseline, so most of the rest of the performance hit is from the Let indirection.

Sub TimeCls()
   Dim i As Long
   Dim timer As New CTimer
   Dim participants(1 To 10000) As clsParticipant
   For i = 1 To 10000
       Set participants(i) = New clsParticipant
   Next i

   timer.StartCounter
   For i = 1 To 10000
      'participants(i).Name = "TestName"
      'participants(i).Gender = 1
      participants(i).sName = "TestName"
      participants(i).iGender = 1
   Next
   Debug.Print "Elapsed time: " & timer.TimeElapsed & " ms"
End Sub

Elapsed time: 1.71887815565976 ms