Optimize String Handling in Visual Basic

[sheeeng]

Hi all,
I just want to share some info here. Do I post at the right place? If not, pls forgive me.

Below is the link to an article about string in VB. Only after reading, I know vbNullString is better than "". After I implemented it in one of my macro, vbNullString cut execution time down by half and more!

Optimize string handling in Visual Basic
http://www.aivosto.com/vbtips/stringopt.html

Thanks.

[MOS MASTER]

Hi,

Always nice to read a good article...thnx!

[TonyJollans]

Hi sheeeng,

An interesting article. One I would recommend serious VBA-ers to read, but ...

FWIW, my take on this is that one deliberately sacrifices performance for other gains when using a language such as VBA. If performance really matters I would use another language (although it might be cheaper to buy a faster chip and stick with VBA).

Some of the tricks in the article seem to sacrifice readability and maintainability in particular - if you are comfortable with the code and no-one else is going to have to work on it then fine, but in a corporate environment the overhead of someone unfamiliar with some of the techniques having to understand the code is quite possibly more expensive than the performance saving.

A couple of (to me) interesting points:

I can see that changing "" to vbNullString might give some benefit, but cutting execution time by half. WOW !

vbNewLine faster than vbCr? I would have expected the opposite as vbNewLine has more processing to do - the article doesn't say so but one should always use vNewLine anyway as it's platform-independent.

[MOS MASTER]

Hi Tony,

The vbNullString part was a chocker to me as well!
I new it was faster...but I doubt if it cuts processing time in half..

I use it anyway as it is good practice.

[Ken Puls]

...but I doubt if it cuts processing time in half..

For that line, maybe, but not the whole procedure! LOL!

[MOS MASTER]

For that line, maybe, but not the whole procedure! LOL!

I kind a thought the same thing!

[sheeeng]

Just to share the knowledge wit everyone...

[Ken Puls]

Hey Sheeng,

Not dissing the article at all, just putting forward our opinions on it. I actually came across it a couple of years ago, and I also have changed my coding to use vbNullString over "".

Great Avatar, btw!

[TonyJollans]

Please carry on sharing, sheeeng.

I found the article, and others on the site, interesting and, as I said, I recommend others to read it. The pointer to it is a valuable addition to VBAX.

I would guess that the majority of members and visitors here don't even know there is a difference between a null string and an empty string, never mind what the difference is, or how to distinguish between the two if it matters.

[Jacob Hilderbrand]

I always thought that "" should be used over vbNullString. So which is really better? We can easily put it to the test.
[vba]
Option Explicit

Sub StringTest()

Dim MyTimer As Double
Dim i As Long
Dim j As Long

Application.ScreenUpdating = False

For j = 1 To 20
Range("A:A").ClearContents
Range("B1").Value = "Double Quotes"
MyTimer = Timer
For i = 1 To 500
If Range("A" & i).Value = "" Then
Range("A" & i).Value = 1
End If
Next i
Range("B65536").End(xlUp).Offset(1, 0).Value = Timer - MyTimer
Next j
For j = 1 To 20
Range("A:A").ClearContents
Range("C1").Value = "vbNullString"
MyTimer = Timer
For i = 1 To 500
If Range("A" & i).Value = vbNullString Then
Range("A" & i).Value = 1
End If
Next i
Range("C65536").End(xlUp).Offset(1, 0).Value = Timer - MyTimer
Next j
For j = 1 To 20
Range("A:A").ClearContents
Range("D1").Value = "Len"
MyTimer = Timer
For i = 1 To 500
If Len(Range("A" & i).Value) = 0 Then
Range("A" & i).Value = 1
End If
Next i
Range("D65536").End(xlUp).Offset(1, 0).Value = Timer - MyTimer
Next j
Range("B22").Value = "=Average(B2:B21)"
Range("C22").Value = "=Average(C2:C21)"
Range("D22").Value = "=Average(D221)"
Range("B").EntireColumn.AutoFit

Application.ScreenUpdating = True

End Sub
[/vba]

I ran this code 6 times now, and vbNullString was never the fastest for me. It went back and forth from "" and Len. Though all three were very close together (within less than .1% of the min).

So which is fastest? I can't say with 100% certainty, though I can say that none of the methods are 50% faster than the other.

And given the speed of today's PCs, a small performance gain is usually not worth much. So just code the way you are comfortable.

[sheeeng]

I always thought that "" should be used over vbNullString. So which is really better? We can easily put it to the test.
[vba]
Option Explicit

Sub StringTest()

Dim MyTimer As Double
Dim i As Long
Dim j As Long

Application.ScreenUpdating = False

For j = 1 To 20
Range("A:A").ClearContents
Range("B1").Value = "Double Quotes"
MyTimer = Timer
For i = 1 To 500
If Range("A" & i).Value = "" Then
Range("A" & i).Value = 1
End If
Next i
Range("B65536").End(xlUp).Offset(1, 0).Value = Timer - MyTimer
Next j
For j = 1 To 20
Range("A:A").ClearContents
Range("C1").Value = "vbNullString"
MyTimer = Timer
For i = 1 To 500
If Range("A" & i).Value = vbNullString Then
Range("A" & i).Value = 1
End If
Next i
Range("C65536").End(xlUp).Offset(1, 0).Value = Timer - MyTimer
Next j
For j = 1 To 20
Range("A:A").ClearContents
Range("D1").Value = "Len"
MyTimer = Timer
For i = 1 To 500
If Len(Range("A" & i).Value) = 0 Then
Range("A" & i).Value = 1
End If
Next i
Range("D65536").End(xlUp).Offset(1, 0).Value = Timer - MyTimer
Next j
Range("B22").Value = "=Average(B2:B21)"
Range("C22").Value = "=Average(C2:C21)"
Range("D22").Value = "=Average(D221)"
Range("B").EntireColumn.AutoFit

Application.ScreenUpdating = True

End Sub
[/vba]

I ran this code 6 times now, and vbNullString was never the fastest for me. It went back and forth from "" and Len. Though all three were very close together (within less than .1% of the min).

So which is fastest? I can't say with 100% certainty, though I can say that none of the methods are 50% faster than the other.

And given the speed of today's PCs, a small performance gain is usually not worth much. So just code the way you are comfortable.

I would agree on this...small performance gain is not significantly affecting our life nowadays....

Well, maybe in a larger scale of code, it might have a diffrence...
Who knows?

[Ken Puls]

Was chatting with Jake on YIM here, and I ran up a longer test than he did, and came up with some different results. Ain't as perty and you have to add your own average formula right now, but:

[vba]Sub b()

Dim MyTimer As Double
Dim i As Long
Dim j As Long

Application.ScreenUpdating = False
Range("C1").Value = "vbnullstring"
Range("D1").Value = "2 quotes"
For j = 1 To 50
Range("A:A").ClearContents
MyTimer = Timer
For i = 1 To 10000
If Range("A" & i).Value = vbNullString Then
Range("A" & i).Value = 1
End If
Next i
Range("C65536").End(xlUp).Offset(1, 0).Value = Timer - MyTimer
Next j
For j = 1 To 50
Range("A:A").ClearContents
MyTimer = Timer
For i = 1 To 10000
If Range("A" & i).Value = "" Then
Range("A" & i).Value = 1
End If
Next i
Range("D65536").End(xlUp).Offset(1, 0).Value = Timer - MyTimer
Next j
End Sub[/vba]

For me, after 50 loops, vbnullstring = .8225, "" = 1.51838

The weird part is that while vbnullstring starts and stays fast, "" seems to slow down after a few iterations.

[sheeeng]

Was chatting with Jake on YIM here, and I ran up a longer test than he did, and came up with some different results. Ain't as perty and you have to add your own average formula right now, but:

[vba]Sub b()

Dim MyTimer As Double
Dim i As Long
Dim j As Long

Application.ScreenUpdating = False
Range("C1").Value = "vbnullstring"
Range("D1").Value = "2 quotes"
For j = 1 To 50
Range("A:A").ClearContents
MyTimer = Timer
For i = 1 To 10000
If Range("A" & i).Value = vbNullString Then
Range("A" & i).Value = 1
End If
Next i
Range("C65536").End(xlUp).Offset(1, 0).Value = Timer - MyTimer
Next j
For j = 1 To 50
Range("A:A").ClearContents
MyTimer = Timer
For i = 1 To 10000
If Range("A" & i).Value = "" Then
Range("A" & i).Value = 1
End If
Next i
Range("D65536").End(xlUp).Offset(1, 0).Value = Timer - MyTimer
Next j
End Sub[/vba]

For me, after 50 loops, vbnullstring = .8225, "" = 1.51838

The weird part is that while vbnullstring starts and stays fast, "" seems to slow down after a few iterations.

Hmm....That's a very good research on what u have done...Keep it up ....
I sure there is a reason for what is happening...

[Jacob Hilderbrand]

Ok, when I run Ken's code I get this.

"" = .9912
vbNullString = 1.01062

About a 2 percent difference (on 500,000 iterations).

[Ken Puls]

Okay, with more runs, the time differences for me seem to be coming in +- 10% either way. ie, no major performance gain stands out over the other.

[Jacob Hilderbrand]

So what happens if we increase the iterations. Let's try 1 million iterations each.

[vba]
Option Explicit

Sub StringTest()

Dim MyTimer As Double
Dim i As Long
Dim j As Long
Dim UpperLimit As Long

Application.ScreenUpdating = False

UpperLimit = 200

For j = 1 To UpperLimit
Range("A:A").ClearContents
Range("B1").Value = "Double Quotes"
MyTimer = Timer
For i = 1 To 5000
If Range("A" & i).Value = "" Then
Range("A" & i).Value = 1
End If
Application.StatusBar = "Loop 1: " & j & " of " & UpperLimit & " (" & i & ")"
Next i
Range("B65536").End(xlUp).Offset(1, 0).Value = Timer - MyTimer
Next j
For j = 1 To UpperLimit
Range("A:A").ClearContents
Range("C1").Value = "vbNullString"
MyTimer = Timer
For i = 1 To 5000
If Range("A" & i).Value = vbNullString Then
Range("A" & i).Value = 1
End If
Application.StatusBar = "Loop 2: " & j & " of " & UpperLimit & " (" & i & ")"
Next i
Range("C65536").End(xlUp).Offset(1, 0).Value = Timer - MyTimer
Next j
For j = 1 To UpperLimit
Range("A:A").ClearContents
Range("D1").Value = "Len"
MyTimer = Timer
For i = 1 To 5000
If Len(Range("A" & i).Value) = 0 Then
Range("A" & i).Value = 1
End If
Application.StatusBar = "Loop 3: " & j & " of " & UpperLimit & " (" & i & ")"
Next i
Range("D65536").End(xlUp).Offset(1, 0).Value = Timer - MyTimer
Next j
Range("B" & UpperLimit + 2).Value = "=Average(B2:B" & UpperLimit & ")"
Range("C" & UpperLimit + 2).Value = "=Average(C2:C" & UpperLimit & ")"
Range("D" & UpperLimit + 2).Value = "=Average(D2" & UpperLimit & ")"
Range("B").EntireColumn.AutoFit

Application.ScreenUpdating = True

End Sub
[/vba]

In my test with this code, Len came out on top with an average of .50704. The other two methods were less than 1% off.

I am running this on Excel 2002 with Windows XP Home 3GHtz CPU with 1 GB Ram.

So basically, choose your method of choice.

[Ken Puls]

Okay, using Jake's last example using XP Pro, Excel 2003, 3.06 Ghz with 512 MB Ram:

Double Quotes = 0.80844
vbNullstring = 1.21585
Len = 1.1453


The procedure takes a while to run though, so I'm not testing it again tonight! LOL!


I don't think that vbNullstring offers any significant performance advantage here at all.

[sheeeng]

Okay, using Jake's last example using XP Pro, Excel 2003, 3.06 Ghz with 512 MB Ram:

Double Quotes = 0.80844
vbNullstring = 1.21585
Len = 1.1453


The procedure takes a while to run though, so I'm not testing it again tonight! LOL!


I don't think that vbNullstring offers any significant performance advantage here at all.

I will try the code on my PC tomolo...Seem wreid here....Hmm...
vbNullString exists for a purpose....
I supposed...

[sheeeng]

Hi all,
I just want to share some info here. Do I post at the right place? If not, pls forgive me.

Below is the link to an article about string in VB. Only after reading, I know vbNullString is better than "". After I implemented it in one of my macro, vbNullString cut execution time down by half and more!

Optimize string handling in Visual Basic
http://www.aivosto.com/vbtips/stringopt.html

Thanks.

I think I need to rephrase my statement here....
vbNullString WILL NOT cut execution time down by half and more!

[sheeeng]

So what happens if we increase the iterations. Let's try 1 million iterations each.

[vba]
Option Explicit

Sub StringTest()

Dim MyTimer As Double
Dim i As Long
Dim j As Long
Dim UpperLimit As Long

Application.ScreenUpdating = False

UpperLimit = 200

For j = 1 To UpperLimit
Range("A:A").ClearContents
Range("B1").Value = "Double Quotes"
MyTimer = Timer
For i = 1 To 5000
If Range("A" & i).Value = "" Then
Range("A" & i).Value = 1
End If
Application.StatusBar = "Loop 1: " & j & " of " & UpperLimit & " (" & i & ")"
Next i
Range("B65536").End(xlUp).Offset(1, 0).Value = Timer - MyTimer
Next j
For j = 1 To UpperLimit
Range("A:A").ClearContents
Range("C1").Value = "vbNullString"
MyTimer = Timer
For i = 1 To 5000
If Range("A" & i).Value = vbNullString Then
Range("A" & i).Value = 1
End If
Application.StatusBar = "Loop 2: " & j & " of " & UpperLimit & " (" & i & ")"
Next i
Range("C65536").End(xlUp).Offset(1, 0).Value = Timer - MyTimer
Next j
For j = 1 To UpperLimit
Range("A:A").ClearContents
Range("D1").Value = "Len"
MyTimer = Timer
For i = 1 To 5000
If Len(Range("A" & i).Value) = 0 Then
Range("A" & i).Value = 1
End If
Application.StatusBar = "Loop 3: " & j & " of " & UpperLimit & " (" & i & ")"
Next i
Range("D65536").End(xlUp).Offset(1, 0).Value = Timer - MyTimer
Next j
Range("B" & UpperLimit + 2).Value = "=Average(B2:B" & UpperLimit & ")"
Range("C" & UpperLimit + 2).Value = "=Average(C2:C" & UpperLimit & ")"
Range("D" & UpperLimit + 2).Value = "=Average(D2" & UpperLimit & ")"
Range("B").EntireColumn.AutoFit

Application.ScreenUpdating = True

End Sub
[/vba]

In my test with this code, Len came out on top with an average of .50704. The other two methods were less than 1% off.

I am running this on Excel 2002 with Windows XP Home 3GHtz CPU with 1 GB Ram.

So basically, choose you method of choice.

I tried your code already...

I am running this on Excel 2003 with Windows XP Pro 2.8GHz CPU with 504 MB Ram.

It took me so long to get results..... About 4 minutes of execution time...

"" = 0.525535

vbNullString =0.453905

Len = 0.4604

Initial testing = vbNullString faster