32bit bug - Slow 'Property Get' procedures

I have come across a nasty VBA bug that makes Property Get procedure calls really slow. This is most likely due to a recent Office update (I have Office365). It only affects Excel on 32 bits.

The bug

Consider a class called Class1 with only the code:

Option Explicit

Public Property Get Something() As Long: End Property

Basically, a single Get property that returns a zero.

When running the following code (in a standard .bas module):

Public Sub TestSpeed()
    Const iterations As Long = 10000
    Dim i As Long
    Dim t As Double
    Dim coll As New Collection
    '
    t = Timer
    For i = 1 To iterations
        coll.Add New Class1
        CallGet coll.Item(coll.Count)
    Next i
    Debug.Print iterations & " loops took " & Round(Timer - t, 3) & " seconds"
End Sub

Sub CallGet(ByVal c As Class1)
    Dim v As Variant
    
    v = c.Something
End Sub

I get a decent timing, as expected:

However, if I add a bunch of other Get properties at the top of the one used in testing then things change. The updated Class1 could look like this:

Option Explicit

Public Property Get Something001() As Long: End Property
Public Property Get Something002() As Long: End Property
Public Property Get Something003() As Long: End Property
Public Property Get Something004() As Long: End Property
Public Property Get Something005() As Long: End Property
Public Property Get Something006() As Long: End Property
Public Property Get Something007() As Long: End Property
Public Property Get Something008() As Long: End Property
Public Property Get Something009() As Long: End Property
Public Property Get Something010() As Long: End Property
Public Property Get Something011() As Long: End Property
Public Property Get Something012() As Long: End Property
Public Property Get Something013() As Long: End Property
Public Property Get Something014() As Long: End Property
Public Property Get Something015() As Long: End Property
Public Property Get Something016() As Long: End Property
Public Property Get Something017() As Long: End Property
Public Property Get Something018() As Long: End Property
Public Property Get Something019() As Long: End Property
Public Property Get Something020() As Long: End Property
Public Property Get Something021() As Long: End Property
Public Property Get Something022() As Long: End Property
Public Property Get Something023() As Long: End Property
Public Property Get Something024() As Long: End Property
Public Property Get Something025() As Long: End Property
Public Property Get Something026() As Long: End Property
Public Property Get Something027() As Long: End Property
Public Property Get Something028() As Long: End Property
Public Property Get Something029() As Long: End Property
Public Property Get Something030() As Long: End Property
Public Property Get Something031() As Long: End Property
Public Property Get Something032() As Long: End Property
Public Property Get Something033() As Long: End Property
Public Property Get Something034() As Long: End Property
Public Property Get Something035() As Long: End Property
Public Property Get Something036() As Long: End Property
Public Property Get Something037() As Long: End Property
Public Property Get Something038() As Long: End Property
Public Property Get Something039() As Long: End Property
Public Property Get Something040() As Long: End Property
Public Property Get Something041() As Long: End Property
Public Property Get Something042() As Long: End Property
Public Property Get Something043() As Long: End Property
Public Property Get Something044() As Long: End Property
Public Property Get Something045() As Long: End Property
Public Property Get Something046() As Long: End Property
Public Property Get Something047() As Long: End Property
Public Property Get Something048() As Long: End Property
Public Property Get Something049() As Long: End Property
Public Property Get Something050() As Long: End Property
Public Property Get Something051() As Long: End Property
Public Property Get Something052() As Long: End Property
Public Property Get Something053() As Long: End Property
Public Property Get Something054() As Long: End Property
Public Property Get Something055() As Long: End Property
Public Property Get Something056() As Long: End Property
Public Property Get Something057() As Long: End Property
Public Property Get Something058() As Long: End Property
Public Property Get Something059() As Long: End Property
Public Property Get Something060() As Long: End Property
Public Property Get Something061() As Long: End Property
Public Property Get Something062() As Long: End Property
Public Property Get Something063() As Long: End Property
Public Property Get Something064() As Long: End Property
Public Property Get Something065() As Long: End Property
Public Property Get Something066() As Long: End Property
Public Property Get Something067() As Long: End Property
Public Property Get Something068() As Long: End Property
Public Property Get Something069() As Long: End Property
Public Property Get Something070() As Long: End Property
Public Property Get Something071() As Long: End Property
Public Property Get Something072() As Long: End Property
Public Property Get Something073() As Long: End Property
Public Property Get Something074() As Long: End Property
Public Property Get Something075() As Long: End Property
Public Property Get Something076() As Long: End Property
Public Property Get Something077() As Long: End Property
Public Property Get Something078() As Long: End Property
Public Property Get Something079() As Long: End Property
Public Property Get Something080() As Long: End Property
Public Property Get Something081() As Long: End Property
Public Property Get Something082() As Long: End Property
Public Property Get Something083() As Long: End Property
Public Property Get Something084() As Long: End Property
Public Property Get Something085() As Long: End Property
Public Property Get Something086() As Long: End Property
Public Property Get Something087() As Long: End Property
Public Property Get Something088() As Long: End Property
Public Property Get Something089() As Long: End Property
Public Property Get Something090() As Long: End Property
Public Property Get Something091() As Long: End Property
Public Property Get Something092() As Long: End Property
Public Property Get Something093() As Long: End Property
Public Property Get Something094() As Long: End Property
Public Property Get Something095() As Long: End Property
Public Property Get Something096() As Long: End Property
Public Property Get Something097() As Long: End Property
Public Property Get Something098() As Long: End Property
Public Property Get Something099() As Long: End Property
Public Property Get Something100() As Long: End Property
Public Property Get Something() As Long: End Property

The new timing is quite bad:

Notes

Through more testing I found the following:

1. The time it takes to run the same TestSpeed method increases if I add more Get properties but only if I add them at the top of the one used. Of course, the time goes down if I remove some of the procedures.
2. It only affects my Excel 32 bit version. It works perfectly fine on Excel 64 bits and also perfectly fine in other apps like Word (on both 32 and 64 bits). Both my 32 and 64 bit Excel are version 2102 (Build 13801.21092) as in version 16.0.13801.21072
3. If I use late binding by replacing:
   Sub CallGet(ByVal c As Class1)
   with:
   Sub CallGet(ByVal c As Object)
   the bug is gone. Of course the late binding is a bit slower than early binding:
   

Questions

Can anyone reproduce the above behaviour?

Does anybody have any idea why the above behaviour is happening?

Besides waiting for Microsoft to fix this, is there anything else I could do to fix the issue?

I will be glad to provide more details if needed (like system info).

Edit #1

As pointed out by @PeterT in his answer, the collections are really slow when used with lots of items and especially if items are retrieved by index.

Unfortunately that was not my intention with the question. Apologies to the readers.

Just to clarify, the issue above is still manifesting when using an array or a Scripting.Dictionary. For example the following code yields the same issue:

Option Explicit

Public Sub TestSpeed()
    Const iterations As Long = 10000
    Dim i As Long
    Dim t As Double
    Dim arr() As Class1: ReDim arr(1 To iterations)
    '
    t = Timer
    For i = 1 To iterations
        Set arr(i) = New Class1
        CallGet arr(i)
    Next i
    Debug.Print iterations & " loops took " & Round(Timer - t, 3) & " seconds"
End Sub

Sub CallGet(ByVal c As Class1)
    Dim v As Variant
    
    v = c.Something
End Sub

Updated timings using array:

Edit #2

Apparently, it does not matter if there are multiple Property Get procedures. Even if the class has only one property, the issue is still there, it's just I haven't noticed. By continuously pressing F5 to run the TestSpeed procedure, I got these results:

Using late binding, I got these:

Edit #3

When opening multiple Excel instances (ALT key down) the issue is gone for all the new instances but not for the initial instance. But that's only if I run the code in a new Excel file. If I simply open a saved Excel file that already has the code then the issue is still there for any instance.

Similarly, if I open the first Excel instance and run the code in a new unsaved file, then the issue is gone. However, when opening any saved file with same code then the issue manifests itself.

Edit #4

In fact, the issue is affecting all apps (Word, Outlook, PPT) but it only manifests itself if the file is saved and reopened. I only ran code in these apps without saving thus wrongly assuming this is an Excel issue only.

I've also tested on AutoCAD 2019 and there is no issue even if loading a saved file. However, AutoCAD does not have the VBA code embedded in the file but rather saved as a '.dvb' separate project file.

Saving a macro-enabled file (ex. xlsb/xlsm) that has no code and then opening and adding the test code works perfectly fine and fast - no issue. However, if when saving the file, there is any code module (even if blank) then when opening the file the issue is there when adding the test code.

I tested your example in 2013-32 with an empty class v with the 100 properties, and only got a small difference in timings. I can only assume something related with your particular setup.

However I'd say your 0.45 sec is slow even in an old system, and the reason for that is your particular use of a large Collection. Two ways to improve -

1. Counter intuitively with large collections it's much faster to use Keys rather than Indexes to retrieve items, populating is only slightly slower with keys. Referencing col.Item(1) is fast but bigger indexes are progressively slower, seems internally the collection is looped to find the given index each time ...

    For i = 1 To iterations
       ' coll.Add New Class1
       ' CallGet coll.Item(coll.Count)
        coll.Add New Class1, CStr(i)
        CallGet coll.Item(CStr(i))
    Next i
   

I'd expect use of Key in this test to be a few times faster than your 0.45s. But in actual usage much better because most of the time here is populating the collection as it gets larger rather than retrieving items from the collection.

2. Instead of a collection use an array...

    ReDim arrClass(1 To iterations) As Class1
    For i = 1 To iterations
        Set arrClass(i) = New Class1
        Call CallGet(arrClass(CStr(i)))
    Next
   

This might be twice as fast again. Better though to test creating + storing the object references and retrieving them separately, they are distinct processes.

However no matter how much faster I appreciate the difference will be trivial until figuring the difference of your tests with an empty class and the 100 properties!