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

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