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 1000…

Accepted Answer

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

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

Solution

Problem

Related problems