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.

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(D2:D21)"
    Range("B:D").EntireColumn.AutoFit
    Application.ScreenUpdating = True
End Sub

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 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 difference...
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:

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

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]

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.

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:D" & UpperLimit & ")"
    Range("B:D").EntireColumn.AutoFit
    Application.ScreenUpdating = True
End Sub

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]

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]

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