CODEWITHSUNDEEP
vbaperformancedictionary
j74nilsson
j74nilsson

Reputation: 390

Getting around performance issues for object type evaluations (Scripting.Dictionary)

I stumbled on a significant performance issue when I tried to optimize my code. What I found out is that TypeName(object) is extremely slow for some type of objects. As an example, running TypeName of a variant with the following types took the following time as measured by this benchmarking module.

Thus running TypeName for a Scripting.Dictionary object takes a significant time. In a loop, using this 2000 times will take almost 1 second!

Upvotes: 1

Views: 75

Answers (1)

j74nilsson
j74nilsson

Reputation: 390

The solution is to use the built in TypeOf construct in VBA. The following code was benchmarked:

Option Explicit

Sub TestTypeName()
    Dim Var1 As Variant
    Dim Var2 As Variant
    Dim Var3 As Variant
    Dim Var4 As Variant
    Dim i As Long
    
    Var1 = 12#
    Set Var2 = New Scripting.Dictionary
    Set Var3 = New Scripting.FileSystemObject
    Set Var4 = New Collection
    For i = 1 To 10000
        If VarIsOfTypeCollection_TN1(Var1) Then 'do stuff
        End If
        If VarIsOfTypeCollection_TN2(Var2) Then 'do stuff
        End If
        If VarIsOfTypeCollection_TN3(Var3) Then 'do stuff
        End If
        If VarIsOfTypeCollection_TN4(Var4) Then 'do stuff
        End If
        If VarIsOfTypeCollection_TO1(Var1) Then 'do stuff
        End If
        If VarIsOfTypeCollection_TO2(Var2) Then 'do stuff
        End If
        If VarIsOfTypeCollection_TO3(Var3) Then 'do stuff
        End If
        If VarIsOfTypeCollection_TO4(Var4) Then 'do stuff
        End If
    Next
End Sub

'Four identical functions checking if argument is collection using TypeaName.
'I have four separate functions to be able to benchmark them separately

Function VarIsOfTypeCollection_TN1(Vin As Variant) As Boolean
    VarIsOfTypeCollection_TN1 = TypeName(Vin) = "Collection"
End Function

Function VarIsOfTypeCollection_TN2(Vin As Variant) As Boolean
    VarIsOfTypeCollection_TN2 = TypeName(Vin) = "Collection"
End Function

Function VarIsOfTypeCollection_TN3(Vin As Variant) As Boolean
    VarIsOfTypeCollection_TN3 = TypeName(Vin) = "Collection"
End Function

Function VarIsOfTypeCollection_TN4(Vin As Variant) As Boolean
    VarIsOfTypeCollection_TN4 = TypeName(Vin) = "Collection"
End Function

'Four identical functions checking if argument is collection using TypeOf.
'I have four separate functions to be able to benchmark them separately

Function VarIsOfTypeCollection_TO1(Vin As Variant) As Boolean
    VarIsOfTypeCollection_TO1 = TypeOf Vin Is Collection
End Function

Function VarIsOfTypeCollection_TO2(Vin As Variant) As Boolean
    VarIsOfTypeCollection_TO2 = TypeOf Vin Is Collection
End Function

Function VarIsOfTypeCollection_TO3(Vin As Variant) As Boolean
    VarIsOfTypeCollection_TO3 = TypeOf Vin Is Collection
End Function

Function VarIsOfTypeCollection_TO4(Vin As Variant) As Boolean
    VarIsOfTypeCollection_TO4 = TypeOf Vin Is Collection
End Function

The following benchmark results was given: enter image description here

Thus, a construct using TypeOf is always very fast (<<1µs) and almost 2000 times faster than a construct using TypeName when applied to a Scripting.Dictionary object.

Upvotes: 1

Related Questions