Len vs blank strings vs vbNullString

In Len or Loop we looked at two different methods for checking the length of a string, to determine which is faster. Using logic we can use the length of a string to determine if a string is empty.

There are few ways to check this, one way being the length of the string. But which one is the fastest? That's the subject of this post. We'll need a test procedure that measures the speed of each loop.

The Test Procedure

This is my informal procedure for testing the speed of each of three possible operations:

1. Checking if Len = 0
2. Checking if a string <> ""
3. Checking if a string <> vbNullString

Sub TestLenCompvbNull()

  Dim i As Long
  Dim str1 As String
  Dim starttimeLen As Single
  Dim endtimeLen As Single
  Dim starttimeEmpty As Single
  Dim endtimeEmpty As Single
  Dim starttimevbNull As Single
  Dim endtimevbNull As Single
  Dim msg As String
  Dim hasValue As Boolean

  Const numberOfLoops As Long = 1000000

  str1 = "Hello World"

  ' use Len
  starttimeLen = Timer
  For i = 1 To numberOfLoops
    hasValue = (Len(str1) > 0)
  Next i
  endtimeLen = Timer

  ' use empty string
  starttimeEmpty = Timer
  For i = 1 To numberOfLoops
    hasValue = (str1 <> "")
  Next i
  endtimeEmpty = Timer

  ' use vbNullString
  starttimevbNull = Timer
  For i = 1 To numberOfLoops
    hasValue = (str1 <> vbNullString)
  Next i
  endtimevbNull = Timer

  msg = "Number of iterations: " & numberOfLoops & vbCrLf
  msg = msg & "Using Len: " & _
    Format(endtimeLen - starttimeLen, "#.###") & " seconds" & vbCrLf
  msg = msg & "Using Empty String: " & _
    Format(endtimeEmpty - starttimeEmpty, "#.###") & " seconds" & vbCrLf
  msg = msg & "Using vbNullString: " & _
    Format(endtimevbNull - starttimevbNull, "#.###") & " seconds"

  MsgBox msg
End Sub

Since we are doing a logical comparison, we use a boolean variable to save the result in our loop. This is purely academic, of course, since we never do anything with the result, but only for the purpose of testing the speed of the comparison.

Results

We start with 1,000,000 iterations to find a minor difference, but not enough to consider one or the other faster.

At 10,000,000 iterations we start to see an appreciable difference:

At 100 million iterations the gap is the same, but clearly Len is proving to be fastest.

At a final series of 1 billion iterations the gap is still the same, but I think you can feel the difference between 19 and 33 seconds.

A contributing factor to the overall speed of each loop is the assignment to a boolean. The loop has to do a string comparison first, then cast the result into a boolean variable, which might explain why the loops are a bit slower than the loops in a previous post. You might have noticed that the equivalent Len loops in that post are slightly faster.

Also to be considered is the difference between "" and vbNullString. According to Excel's Visual Basic Help, vbNullString is not the same as a zero-length string. This leads me to believe that there is something going on in the background that causes it to be slower when doing a string comparison. However when I type ?vbNullString = "" into the Immediate Window it returns True so I'm not sure what is going on.

Conclusion

Len is the fastest and also the most intuitive method, so I will continue to use Len for measuring the length of strings, and therefore determine if a string is empty.

I've seen and used vbNullString at times, but I don't see the point. It is supposed to be faster, but it's mentioned specifically "for calling external procedures". I have no idea what that means. I found that it was actually slower, so I won't be using it any more.

To my readers: Have a safe and happy New Year holiday, I will resume posting next week.