我测试了比较速度:
Dim counter As Long区分大小写的二进制比较(默认):0,016秒:
For counter = 1 To 100000
If InStr("This is macro speed test", "macro") > 0 Then
End If
Next counter与大小写转换的不区分大小写比较:0.068秒:
For counter = 1 To 100000
If InStr(UCase("This is macro speed test"), "MACRO") > 0 Then
End If
Next counter不区分大小写vbtextCompare: 0,08秒:
For counter = 1 To 100000
If InStr(1, "This is macro speed test", "MACRO", vbTextCompare) > 0 Then
End If
Next counter我想知道为什么text compare在这次测试中表现得这么差,我看到人们在论坛上推荐它,而不是“慢速”的UCase或LCase转换。
有没有更快的不区分大小写的比较方法?
发布于 2022-01-25 13:50:59
请记住,UCase()和LCase()返回的是不同的结果。您可以通过添加强制字符串结果的"$“:UCase$()和LCase$()来优化它们的使用。
这同样适用于Left$()、Right$()、Mid$()等。
有了这些
Sub test()
Dim counter As Long
Dim start As Double, elapsed As Double
start = Timer
For counter = 1 To 100000
If InStr("This is macro speed test", "macro") > 0 Then
End If
Next counter
elapsed = Timer - start
Debug.Print "1: " & elapsed
start = Timer
For counter = 1 To 100000
If InStr(UCase$("This is macro speed test"), "MACRO") > 0 Then
End If
Next counter
elapsed = Timer - start
Debug.Print "2: " & elapsed
start = Timer
For counter = 1 To 100000
If InStr(1, "This is macro speed test", "macro", vbTextCompare) > 0 Then
End If
Next counter
elapsed = Timer - start
Debug.Print "3: " & elapsed
End Sub我得到了:
1: 0,00390625
2: 0,015625
3: 0,0625对于第二种方法,这稍微快一些。
发布于 2016-12-02 12:33:45
当我运行以下命令时:
Sub test()
Dim counter As Long
Dim start As Double, elapsed As Double
start = Timer
For counter = 1 To 100000
If InStr("This is macro speed test", "macro") > 0 Then
End If
Next counter
elapsed = Timer - start
Debug.Print "1: " & elapsed
start = Timer
For counter = 1 To 100000
If InStr(UCase("This is macro speed test"), UCase("macro")) > 0 Then
End If
Next counter
elapsed = Timer - start
Debug.Print "2: " & elapsed
start = Timer
For counter = 1 To 100000
If InStr(1, "This is macro speed test", "macro", vbTextCompare) > 0 Then
End If
Next counter
elapsed = Timer - start
Debug.Print "3: " & elapsed
End Sub我得到的输出如下:
1: 0.0078125
2: 0.0703125
3: 0.0781252)和3)之间的差异已经缩小到微不足道。看起来你的第二个代码使用的是目标“宏”是大写的,而第三种方法没有假设第三个参数的大小写(因此可能在幕后将它们都转换为大写或小写)。
我多次运行您的原始代码,并确认第二个代码确实比第三个代码具有持久的速度优势,这表明如果您正在根据已知大小写的固定字符串检查大量字符串,那么转换为大小写敏感的搜索并使用vbTextCompare搜索略好于使用vbTextCompare进行不区分大小写的搜索,尽管差异非常小,不太可能产生任何可察觉的差异。
https://stackoverflow.com/questions/40924337
复制相似问题