vba - 解决对象类型评估的性能问题 (Scripting.Dictionary)

Question

当我尝试优化我的代码时，我偶然发现了一个重大的性能问题。我发现 TypeName(object) 对于某些类型的对象来说非常慢。例如，运行具有以下类型的变体的 TypeName 需要以下时间，这是由该基准测试模块测量的。

• 双倍为 0.3 µs
• Scripting.Dictionary 为 402 µs
• Scripting.FilesystemObject 为 1.3 µs
• 收集时间为 1.0 µs

因此，为 Scripting.Dictionary 对象运行 TypeName 需要很长时间。在一个循环中，使用这 2000 次将需要将近 1 秒！

标签： vbaperformancedictionary

Answer 1

解决方案是使用 VBA 中内置的 TypeOf 构造。以下代码进行了基准测试：

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

给出了以下基准测试结果：

因此，当应用于 Scripting.Dictionary 对象时，使用 TypeOf 的构造总是非常快 (<<1µs)，并且比使用 TypeName 的构造快近 2000 倍。