如何在VBA中更精准地测量代码执行时间？

Timer函数的分辨率只有约16毫秒（1/60秒），向Dictionary添加单条条目耗时通常远低于这个阈值，所以会显示0。你可以用Windows API提供的高精度计时方案，分辨率可达微秒级，完全能捕捉这类短耗时操作。

实现步骤 #

1. 声明Windows API函数
   在VBA模块顶部添加以下声明（兼容32位和64位Office）：

#If VBA7 Then
    Declare PtrSafe Function QueryPerformanceCounter Lib "kernel32" (lpPerformanceCount As Currency) As Long
    Declare PtrSafe Function QueryPerformanceFrequency Lib "kernel32" (lpFrequency As Currency) As Long
#Else
    Declare Function QueryPerformanceCounter Lib "kernel32" (lpPerformanceCount As Currency) As Long
    Declare Function QueryPerformanceFrequency Lib "kernel32" (lpFrequency As Currency) As Long
#End If

2. 编写计时逻辑
   用以下代码包裹你要测量的Dictionary操作：

Dim startTime As Currency, endTime As Currency, freq As Currency
Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")

' 初始化计时器
QueryPerformanceFrequency freq
QueryPerformanceCounter startTime

' 执行要测量的操作：向Dictionary添加条目
dict.Add "Key1", "Value1"
' 若单条操作耗时过短，可循环多次放大耗时，方便计算单次平均耗时
' For i = 1 To 10000
'     dict.Add "Key" & i, "Value" & i
' Next i

QueryPerformanceCounter endTime

' 计算耗时（单位：秒）
Dim elapsed As Double
elapsed = (endTime - startTime) / freq

MsgBox "耗时：" & elapsed & " 秒"

补充说明 #

• 单条操作耗时极短时，建议循环执行多次，再将总耗时除以循环次数，得到单次操作的平均耗时，结果会更准确。
• 该API在所有Windows系统上均支持，兼容性良好。

内容的提问来源于stack exchange，提问作者Robert Fey