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

My guess is you are looing a scalability problems and not necessarily slow execution. If you do the test on a small number of iterations I would guess they execute in a tiny fraction of 300ms each. If your designed really needs two million instances of a class in RAM I think you better look at a different design. — rheitzman, Apr 15 '14 at 15:02
Yes, you have encounterd the same issue with class objects that I had to deal with a few years back. I got 'drafted' to analyze a huge access database that had gone into production - worldwide - and it ran like a dog. I placed timers inside every function and subroutine and found the references to the classes were insane. I switched to a few global variables and bingo! I never bothered to understand why those specific ones were slow, but it had to do with the initialization (and no, they were very simple classes). — Wayne G. Dunn, Apr 15 '14 at 15:06
@rheitzman : unfortunately, as this is a large project that is being remodelled, changing the design is not an option at the moment. It's true that running it for instance for 10.000 objects it would take 0ms, but the real problem has a lot of properties to be set and a small number (the number of opened workbooks) instances. It is used to save the data that was used in the calculation of each of the open workbooks. So the UDT isn't an option due to a too large dataset and the class module is too slow. — rperz86, Apr 15 '14 at 15:18
@WayneG.Dunn : if I understand correctly, you were using global variables outside of the class module? we need to have these as instances, or we would have to keep a separate array of the settings in another module. And as far as initialization goes: I start the timer after initializing the array of class instances, so I guess that wouldn't be the problem in this case. — rperz86, Apr 15 '14 at 15:20
Yes, I selected the 'bad' classes and converted to global variables in a 'Global' module (also, some classes were ok, so they were retained as-is). Our issue was the initialization and referencing took too much time -- for a relatively simple class. If you don't include the timer for initialization, how do you get a true reading for what the user sees? In our case, the 'splash' screen was taking almost a minute to load -- I got that down to less than a second (with other changes also!!) — Wayne G. Dunn, Apr 15 '14 at 15:50
I _really_ don't understand the 3 "lacks sufficient information" close votes on this question. It is clearly written and contains an [SSCCE](http://www.sscce.org/) demonstrating the problem. It is of much higher quality than the standard fare for excel, vba tags. — A. Webb, Apr 15 '14 at 16:28
Thanks to rheitzman and @WayneG.Dunn as well for trying to help. Wayne: I didn't time the initialization because I saw that initialization did not add anything to the runtime, although that was a wrong interpretation of the results. — rperz86, Apr 15 '14 at 18:54

score 12 · Accepted Answer · edited May 23 '17 at 10:29

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

Great response! Thanks a lot, I had the realization that I was probably seeing the initialization due to asynchronous creation of the instances, but I didn't have the time yet to test this until now and I see you've already done this for me. Thanks for taking the time to explain this. I thought that the class fields where forced to be private in VBA. Evidently I still have some stuff to learn. Thanks for taking the time to test and explain this to me! — rperz86, Apr 15 '14 at 18:51

score 0 · Answer 2 · answered Apr 16 '14 at 05:05

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

We were trying to isolate the cost of field access (setting), so we purposefully excluded the object creation and destruction time. However, these are valid considerations if the objects are to have short lives. I'm not terribly surprised at your claim that order of destruction can affect performance. VBA uses reference counting, so may well have to propagate more decrements of reference counts depending on relationships and order. — A. Webb, Apr 17 '14 at 02:24
@stenci TimeCls_b and TimeCls_c are identical, and, most important, you're measuring (more or less) sub 0.01s times with a timer with 0.01s resolution. — 6diegodiego9, Jul 03 '22 at 08:44
With the high res timer the results are like this but very oscillating: TimeCls_a: 0.010600>0.021316s TimeCls_b: 0.002172>0.020075s TimeCls_c: 0.002379>0.020213s TimeCls_c_up: 0.010728>0.019822s TimeCls_c_dn: 0.011145>0.019543s TimeCls_c_all: 0.011086>0.019948s — 6diegodiego9, Jul 03 '22 at 08:44
@6diegodiego9 Using "Const NCYCLES = 100000" the smallest time I measure on my current computer is above 0.5 sec — stenci, Jul 04 '22 at 18:46

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

2 Answers2