Optimize String Handling in Visual Basic

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