将字符串变量转换为整数的最有效的 VBA 代码
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12095696/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Most efficient VBA code to convert string variable to integer
提问by Azmisov
I have the following code that converts a string of the following format:"G##"to an integer ##
我有以下代码将以下格式的字符串转换为"G##"整数##
Dim str As String
Dim int As Integer
str = "G14"
int = CInt(Right(str, Len(str) - 1))
But this is going to be run on a large database regularly.
但这将定期在大型数据库上运行。
I am wondering if there are any alternatives that may be more efficient (especially in regards to the last line)?
我想知道是否有任何可能更有效的替代方法（特别是关于最后一行）？
回答by jac
I tried your code against int = CInt(Mid$(str, 2)) for 100,000,000 iterations and the Mid$ statement is slightly faster (6 seconds on my machine), but that was a lot of iterations. However when I added a $to your Right function they ran at the same speed. Using the string function Right$is better optimized for strings than the variant version Right. So the only suggestion I have to use the string optimized version Right$.
我针对 int = CInt(Mid$(str, 2)) 尝试了您的代码进行了 100,000,000 次迭代，Mid$ 语句稍微快一些（在我的机器上为 6 秒），但这是很多迭代。但是，当我向您的 Right 函数添加$ 时，它们以相同的速度运行。Right$与变体版本相比，使用字符串函数对字符串进行了更好的优化Right。所以唯一的建议是我必须使用字符串优化版本 Right$。
Dim str As String
Dim int As Integer
str = "G14"
int = CInt(Right$(str, Len(str) - 1))
回答by brettdj
On my testing using
在我的测试中使用
Mid$(strTest, 1, 1) = "0"
lngTest = CLng(strTest)
was 30-40% faster then using
比使用快 30-40%
CLng(Right$(strTest, Len(strTest) - 1))
which in turn was signififcantly faster than
这反过来明显快于
CLng(Right(strTest, Len(strTest) - 1))
I used Longas it is superior speed wise to Integer
我用过，Long因为它是卓越的速度明智的Integer
For multiple replacements a RegExpmay come into it's own. The overhead is too high to justify it for this sample
对于多次替换，aRegExp可能会出现。开销太高，无法证明此示例的合理性
Test Code
测试代码
Sub Test()
Dim dbTime As Double
Dim strTest As String
Dim lngTest As Long
Dim lngCnt As Long
strTest = "G14"
dbTime = Timer
For lngCnt = 1 To 1000000
lngTest = CLng(Right$(strTest, Len(strTest) - 1))
Next lngCnt
Debug.Print Timer - dbTime
dbTime = Timer
For lngCnt = 1 To 1000000
lngTest = CLng(Right(strTest, Len(strTest) - 1))
Next lngCnt
Debug.Print Timer - dbTimer
dbTime = Timer
For lngCnt = 1 To 1000000
Mid$(strTest, 1, 1) = "0"
lngTest = CLng(strTest)
Next lngCnt
Debug.Print Timer - dbTime
End Sub
