VBA中For的结束值在不同赋值方式下的耗时

概述

在设置For的开始和结束值时，往往会使用公式，即需要计算才能得到值。但For会不会每次循环都计算一次结束值呢，那岂不是很浪费时间？这就需要来测试一下。

测试速度的代码

Private Declare Function QueryPerformanceCounter Lib "KERNEL32" (lpPerformanceCount As Currency) As Long
Private Declare Function QueryPerformanceFrequency Lib "KERNEL32" (lpFrequency As Currency) As Long

Private m_Frequency   As Currency
Private m_Start       As Currency
Private m_Now         As Currency
Private m_Available   As Boolean

Sub TestSpeet()

m_Available = (QueryPerformanceFrequency(m_Frequency) <> 0)
If Not m_Available Then
Debug.Print "Performance Counter not available"
End If

Dim i As Long
Dim a(1000) As Long, b As Long, c(1) As Long

QueryPerformanceCounter m_Start

For i = 1 To 1000000 Step 1
    ' 下面的代码块中选一个执行
    ' --------------- 循环时求值 ---------------
    For j = 1 To UBound(a) Step 1
    Next j
    ' --------------- 循环前求值 ---------------
    b = UBound(a)
    For j = 1 To b Step 1
    Next j
    ' --------------- 常数 ---------------
    For j = 1 To 1000 Step 1
    Next j
Next i

QueryPerformanceCounter m_Now

Elapsed = 1000 * (m_Now - m_Start) / m_Frequency
Debug.Print Format(Elapsed, "#.0000")
End Sub

测试结果

测试结果的时间单位是毫秒。

测试顺序	循环时求值	循环前求值	常数
1	360.2659	360.0748	359.8621
2	360.0173	360.4622	360.0325
3	360.0655	359.9629	359.6281
4	359.8392	359.9041	359.7904
5	360.2690	360.3178	360.0977
6	360.2365	360.2665	359.7168
7	360.1620	359.8445	360.4467
8	360.1632	360.2971	360.3351
9	360.1063	359.9023	360.1060
10	359.7458	360.6394	365.0117

结论

所以可以尽情把For的起止值设为公式，因为这并不比特地定义一个变量来存放要慢。