VBA中Long和String分别做布尔运算的耗时

概述

用if做判断时，数据类型对速度的影响有多大？是否值得添加修改类型的代码？

测试速度的代码

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



Private Declare Sub CopyMemory Lib "kernel32.dll" Alias "RtlMoveMemory" (ByRef Destination As Any, ByVal Source As Any, ByVal Length As Long)
Sub asdf()
Dim i As Long
For i = 1 To 1000 Step 1
    a = a & Chr(Int((90 - 65 + 1) * Rnd + 65))
Next i
Cells(1, 1) = a
Debug.Print a
End Sub

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

Dim a As Long, b As Long
a = 231312
b = 234324
Dim c As String, d As String
c = "as"
d = "er"
Dim e As String, f As String
e = "er"
f = "erf"

Dim i As Long
QueryPerformanceCounter m_Start
For i = 1 To 1000000 Step 1

' 以下三个 if 选一个执行
If a = b Then
End If
If c = d Then
End If
If e = f Then
End If

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

测试结果

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

测试序号	long型	长度相同的string型	长度不同的string型
1	10.4342	40.8870	40.6155
2	10.4964	40.9349	41.5193
3	10.5122	40.9706	41.3557
4	10.5648	40.9775	40.9318
5	10.5694	41.0120	40.9654
6	10.5968	41.0207	40.6002
7	10.6139	41.0804	41.3952
8	10.6167	41.1190	41.0636
9	10.6624	41.1336	40.8099
10	10.6886	41.1607	40.8892

结论

和数值相比，字符串比较慢太多，多以完全值得花费时间来优化代码，把参与布尔运算的数据类型做成数值。