最有效的VBA代码将字符串变量转换为整数 [英] Most efficient VBA code to convert string variable to integer

问题描述

我有以下代码可以转换以下格式的字符串:
"G##"转换为整数##

I have the following code that converts a string of the following format:
"G##" to an integer ##

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)?

推荐答案

使用我的测试

 Mid$(strTest, 1, 1) = "0"
 lngTest = CLng(strTest)

使用速度要快30-40％

was 30-40% faster then using

CLng(Right$(strTest, Len(strTest) - 1))

反过来比

CLng(Right(strTest, Len(strTest) - 1))

我使用了Long，因为它在速度上要优于Integer

I used Long as it is superior speed wise to Integer

对于多个替代品，RegExp可能会自己使用.该示例的开销太大，无法证明其合理性

For multiple replacements a RegExp may come into it's own. The overhead is too high to justify it for this sample

测试代码

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

这篇关于最有效的VBA代码将字符串变量转换为整数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！