So what happens if we increase the iterations. Let's try 1 million iterations each.

[vba]

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

" & UpperLimit & ")"

Range("B

").EntireColumn.AutoFit

Application.ScreenUpdating = True

End Sub

[/vba]

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 you method of choice.