CODEWITHSUNDEEP
vba
Dam Gal
Dam Gal

Reputation: 145

VBA class performance

I've got this code:

For i = 0 To .Fields.Count - 1
    Set qc = qcolumns(i)
    If qc.XlGrouped = True Then
...

What is weird for me is that if I change qc.XlGrouped = True to qcolumns(i).XlGrouped = True it will take 200ms while the first one takes 0ms. Why such a difference just from setting class from an array to a variable?

Upvotes: 1

Views: 366

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71187

The 200ms is anecdotal, but the overhead can be explained.

Dim qcolumns As Variant

That's your array, but to VBA it's just a Variant: in order to know it's looking at an array, VBA needs (at runtime) to dereference the variant pointer, inspect the bytes that hold the subtype metadata, get the array pointer, ...and dereference the array pointer.

There would be less overhead with an explicit array declaration:

Dim qcolumns(1 To 10) As SettingsColumns

Now, we don't really care about the array... we're just trying to iterate its contents:

Set qc = qcolumns(i)

So at each iteration, the variant/array dereferencing needs to happen, and the object pointer gets copied to qc. But why is it slower to make a member call against qcolumns(i)?

Because qcolumns is a Variant, any member call made against it has to be late-bound, i.e. resolved at run-time. The XlGrouped property isn't known to VBA until the member call succeeds, and the member call can only succeed after querying the IUnknown interface of the object (if that fails, error 438 is raised).

When you do Dim qc As SettingsColumn, you are binding early, at compile-time. Now the compiler already knows where to find the XlGrouped member, and doesn't need to query IUnknown through a Variant pointer: everything is much simpler... and usually simpler means faster.

But you're iterating an array of objects: that's going to be slower than it needs to be, no matter what.

Objects want to be iterated in a Collection, using a For Each loop.

Dim qc As SettingsColumn
For Each qc In myCollection
    If qc.XlGrouped Then  '<~ note =True is redundant
       '...
    End If
Next

See For vs For Each for more information and performance benchmarks. The bottom line is, iterating an object Collection with a For loop and retrieving each item by index, is always going to be much slower than a For Each loop; use For to iterate arrays of values, For Each to iterate collections of objects.

Upvotes: 4

Related Questions