Results 1 to 20 of 37

Thread: Optimize String Handling in Visual Basic

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #16
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •