Optimize String Handling in Visual Basic

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