Benchmarking Guidance

This page explains how to use cPerformanceManager for meaningful benchmarking in Excel VBA.

The class gives you a strong timing surface, but good results still depend on how you design the benchmark itself.

What benchmarking means here

In this context, benchmarking means measuring the time cost of some VBA, worksheet, calculation, or Excel interaction pattern as consistently and fairly as possible.

Typical examples include:

• comparing two implementations of the same logic
• measuring worksheet write speed
• measuring recalculation behavior
• measuring object-model overhead
• measuring repeated-loop cost
• measuring the impact of TW suppression

First rule: measure the right thing

Before writing any timing code, define exactly what you want to measure.

Examples:

• only the assignment into a range
• assignment plus recalculation
• full end-user workflow including screen refresh
• raw VBA loop cost without worksheet interaction
• worksheet interaction with Excel noise reduced

Use the recommended default unless you have a reason not to

For most benchmark work, use:

• StartTimer with the default method
• or StartTimer 5

Method 5 (QPC) is the preferred benchmark path.

Prefer repeated runs over single-run conclusions

Run the same benchmark multiple times and compare:

• minimum
• maximum
• average
• spread

Warm up before serious measurement

The first execution can differ from later executions because of:

• workbook state changes
• cache effects
• formula graph activity
• one-time timer-resolution activation for method 3
• object creation overhead

Use ElapsedSeconds for logic and ElapsedTime for reporting

Use ElapsedSeconds when:

• storing numeric results
• comparing runs
• aggregating statistics
• building summaries
• calculating averages or ratios

Use ElapsedTime when:

• printing a human-readable line
• logging a demo result
• showing a readable diagnostic string

Use TW suppression when the benchmark calls for it

TW suppression can help reduce Excel-side interference. It is useful when benchmarking:

• worksheet writes
• formula assignment
• recalculation-heavy work
• code sensitive to screen/event overhead

But do not assume TW suppression is always “more correct.” Sometimes the real user path includes UI, events, or normal Excel behavior, and suppressing those would make the benchmark less representative.

Recommended benchmark harness pattern

Option Explicit

Public Sub Benchmark_RecommendedPattern()

    Dim cPM         As cPerformanceManager
    Dim RunIdx      As Long
    Dim ElapsedS    As Double
    Dim BestS       As Double
    Dim WorstS      As Double
    Dim TotalS      As Double

    On Error GoTo CleanFail

    Set cPM = New cPerformanceManager

    BestS = 1E+30
    WorstS = 0#

    'Optional warm-up.
        Range("A1:A10000").ClearContents
        Range("A1:A10000").Value = 1
        Range("A1:A10000").ClearContents

    For RunIdx = 1 To 10

        Range("A1:A10000").ClearContents

        cPM.StartTimer 5

        Range("A1:A10000").Value = 1

        ElapsedS = cPM.ElapsedSeconds

        If ElapsedS < BestS Then BestS = ElapsedS
        If ElapsedS > WorstS Then WorstS = ElapsedS
        TotalS = TotalS + ElapsedS

    Next RunIdx

    Debug.Print "Runs  : 10"
    Debug.Print "Best  : " & Format$(BestS, "0.000000000")
    Debug.Print "Worst : " & Format$(WorstS, "0.000000000")
    Debug.Print "Avg   : " & Format$(TotalS / 10#, "0.000000000")

CleanExit:
    If Not cPM Is Nothing Then
        cPM.ResetEnvironment
        Set cPM = Nothing
    End If
    Exit Sub

CleanFail:
    Debug.Print "Error " & Err.Number & " - " & Err.Description
    Resume CleanExit

End Sub