VBA中建立Function的耗时

概述

设计算法时，可以把部分功能做成一个Function，这样既可以使代码更易于阅读，又可以进行代码重用。但其会额外开销硬件资源，这就要测一测到底慢多少。

注：在传递参数时，若使用byval，则要建立参数的副本，对于数据量大的数组，建立副本会很消耗时间，所以建议大家尽量使用byref。本测试中全部用byref，以避免建立副本带来的开销。

测试速度的代码

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 As Long, b As Long, c As Long

QueryPerformanceCounter m_Start

For i = 1 To 1000000 Step 1
    ' 下面五句中选一行执行
    a = b + c          ' 不用函数
    a = ToDo0()        ' 零个参数
    a = ToDo1(a)       ' 一个参数
    a = ToDo2(a, b)    ' 两个参数
    a = ToDo3(a, b, c) ' 三个参数
Next i

QueryPerformanceCounter m_Now

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

Function ToDo0() As Long
ToDo = ToDo + ToDo
End Function
Function ToDo1(a As Long) As Long
ToDo = ToDo + ToDo
End Function
Function ToDo2(a As Long, b As Long) As Long
ToDo = ToDo + ToDo
End Function
Function ToDo3(a As Long, b As Long, c As Long) As Long

测试结果

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

测试顺序	不用函数	零个参数	一个参数	两个参数	三个参数
1	8.0002	89.9137	92.7463	98.8352	101.6376
2	8.0506	89.6261	93.1016	98.8160	101.3602
3	7.9891	89.8076	93.0725	99.5525	101.3064
4	8.0750	89.8039	92.8678	98.6769	101.3559
5	8.0058	89.9245	92.9476	99.6873	101.1559
6	8.1066	89.4638	93.4336	98.9042	101.2353
7	7.9712	89.8521	93.2877	98.7279	100.8427
8	8.0732	89.4768	93.2453	99.2764	101.1488
9	8.2652	89.7897	92.9952	98.6070	101.5662
10	7.9956	90.2504	93.0437	99.0390	101.6802

结论

Function确实额外开销硬件资源。

100万次调用，消耗了81ms，即1亿次，消耗8s。

所以，对于调用次数不多、时间要求不苛刻的情况，完全可以考虑把整篇的代码分割成多个函数。

反之则要少用。比如两个10万行的表进行笛卡尔积对比，哪怕每次对比仅仅调用一次，那也是10万乘以10万，即一百亿次调用，也就是800s，13min。

我采用了两全其美的办法：把所有数据传入函数，而不是每条数据挨个调用函数。