.VBA Perf.md

Performance Tests in VBA

Variant Copy vs Local implementation

Tests:

Private Declare PtrSafe Sub VariantCopyDLL Lib "oleaut32.dll" Alias "VariantCopy" (ByRef pvargDest As Variant, ByRef pvargSrc As Variant)
Const MaxCount As Long = 10 ^ 6

Public Sub VariantCopyLocal(ByRef dest As Variant, ByRef src As Variant)
    If IsObject(src) Then
        Set dest = src
    Else
        dest = src
    End If
End Sub

Sub perfTest()
    Dim i As Long
    Dim v, q
    q = 1
    
    
    With stdPerfTest.Create("VariantCopyDLL")
        For i = 1 To MaxCount
            VariantCopyDLL v, q
        Next
    End With
    With stdPerfTest.Create("VariantCopyLocal")
        For i = 1 To MaxCount
            VariantCopyLocal v, q
        Next
    End With
End Sub

Results

VariantCopyDLL: 1713.448
VariantCopyLocal: 46.4615

Conclusion

Avoid use of VariantCopy DLL. Use a local definition instead.

Function definition location:

Tests

The following function was tested in numerous different scenarios:

Function TestFunction()
End Function

Scenarios tested:

• Public Function in local module
• Private Function in local module
• Public Function in external module
• Public Function in external class
• Public Function in external predecl class

Const MaxCount As Long = 10 ^ 8
Sub Main()
    'Test function calls
    With stdPerfTest.Create("Public Function in local module")
        For i = 1 To MaxCount
            Call FunctionBodyLocal
        Next
    End With
    With stdPerfTest.Create("Private Function in local module")
        For i = 1 To MaxCount
            Call FunctionBodyLocalPrivate
        Next
    End With
    With stdPerfTest.Create("Public Function in external module")
        For i = 1 To MaxCount
            Call FunctionBodyExternalModule
        Next
    End With
    With stdPerfTest.Create("From module to Public Function in external class")
        With New Class1
            For i = 1 To MaxCount
                Call .FunctionBodyExternalClass
            Next
        End With
    End With
    With stdPerfTest.Create("From module to Public Function in external predecl class")
        With New Class2
            For i = 1 To MaxCount
                Call .FunctionBodyExternalClass
            Next
        End With
    End With
    Class2.Main
End Sub

WITHIN CLASS2:

sub main()
    With stdPerfTest.Create("Within Class to internal class function using With Me ... End With")
        With Me
            For i = 1 To MaxCount
                Call Me.FunctionBodyExternalClass
            Next
        End With
    End With
    With stdPerfTest.Create("Within Class to internal class function without using With Me ... End With")
        For i = 1 To MaxCount
            Call Me.FunctionBodyExternalClass
        Next
    End With
    With stdPerfTest.Create("Within Class to Module Function Direct Reference")
        For i = 1 To MaxCount
            Call Module2.FunctionBodyExternalModule
        Next
    End With
end sub

Results

Results have been sorted into 2 groups

'Module function execution
Public Function in local module:                  6110.219
Private Function in local module:                 6142.9171
Public Function in external module:               6147.6899
Within Class to Module Function:                  6245.1253

'Class function execution 
From module to Public Function in external class:                        8443.8625
From module to Public Function in external predecl class:                8382.9995
Within Class to internal function using With Me ... End With:            8647.02
Within Class to internal function without using With Me ... End With:    8651.7356

Conclusions

Public functions "appear" to run faster than private functions in the local module. This is likely largely because private modules are defined towards the end of the module body (after compilation). External function modules are also a tiny bit slower, but realistically there is no major difference while calling module functions from anywhere within the code (even calls from classes have a similar timing to those within modules.

Calls to functions defined in classes, however are significantly (1/3 times) slower to those defined within modules. Expect this is largely due to poor implementation of classes (reference to me, searching for function in class symbol table, etc.)