CODEWITHSUNDEEP
stringexcelvbaoptimization
Azmisov
Azmisov

Reputation: 7253

Most efficient VBA code to convert string variable to integer

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

Upvotes: 1

Views: 36590

Answers (2)

jac
jac

Reputation: 9726

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$.

Dim str As String
Dim int As Integer
str = "G14"
int = CInt(Right$(str, Len(str) - 1))

Upvotes: 2

brettdj
brettdj

Reputation: 55682

On my testing using

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

was 30-40% faster then using

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

which in turn was signififcantly faster than 

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

I used Long as it is superior speed wise to Integer

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

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

Upvotes: 2

Related Questions