Range() VS Cells() - run times

I have done some testing to see what's what.

Method

I have tested the speeds of four scenarios. Each test consisted of a For loop doing 100 000 cycles. The core of the test was using a with statement to "grab" a cell.

For i = 1 To 100000  With Cells(i, 1)  End WithNext i

The four tests were:

• Cells, variable cells - With Cells(i, 1)

• Cells, single cell - With Cells(1, 1)

• Range, variable cells - With Range("A" & i)

• Range, single cell - Range("A1")

I have used separate subs for the four test cases, and used a fifth sub to run each of them 500 times. See the code below.

For time measurement, I have used GetTickCount to get millisecond accuracy.

Results

From 500 measurements, the results were pretty consistent. (I have run it multiple times with 100 iterations, with pretty much the same results.)

          Cells     Cells     Range     Range        (variable) (single) (variable) (single)avg       124,3     126,4     372,0     329,8median     125       125       374       328mode       125       125       374       328stdev      4,1       4,7       5,7       5,4min        109       124       358       327max        156       141       390       344

Interpretation

The Cells method is 2.6 times faster than an equivalent Range method. If concatenation is being used, this adds another 10% execution time, which makes the difference almost 3x. This is a huge difference.

On the other hand though, we are talking about an average of 0.001 ms VS 0.004 ms per cell operation. Unless we are running a script on more than 2-3 hundred thousand cells, this is not going to make a noticeable speed difference.

Conclusion

Yep, there is a huge speed difference.

Nope, I'm not going to bother telling people to use the Cells method unless they process huge amounts of cells.

Test set-up

• Win7 64 bit
• 8 GB RAM
• Intel Core i7-3770 @ 3.40 GHz
• Excel 2013 32 bit

Did I miss anything? Did I cock something up? Please don't hesitate to point it out! Cheers! :)

Code

Public Declare Function GetTickCount Lib "kernel32.dll" () As LongSub testCells(j As Long)  Dim i As Long  Dim t1 As Long  Dim t2 As Long  t1 = GetTickCount    For i = 1 To 100000      With Cells(i, 1)      End With    Next i  t2 = GetTickCount  Sheet4.Cells(j, 1) = t2 - t1End SubSub testRange(j As Long)  Dim i As Long  Dim t1 As Long  Dim t2 As Long  t1 = GetTickCount    For i = 1 To 100000      With Range("A" & i)      End With    Next i  t2 = GetTickCount  Sheet4.Cells(j, 2) = t2 - t1End SubSub testRangeSimple(j As Long)  Dim i As Long  Dim t1 As Long  Dim t2 As Long  t1 = GetTickCount    For i = 1 To 100000      With Range("A1")      End With    Next i  t2 = GetTickCount  Sheet4.Cells(j, 3) = t2 - t1End SubSub testCellsSimple(j As Long)  Dim i As Long  Dim t1 As Long  Dim t2 As Long  t1 = GetTickCount    For i = 1 To 100000      With Cells(1, 1)      End With    Next i  t2 = GetTickCount  Sheet4.Cells(j, 4) = t2 - t1End SubSub runtests()  Application.ScreenUpdating = False  Application.Calculation = xlCalculationManual  Dim j As Long  DoEvents  For j = 1 To 500    testCells j  Next j  DoEvents  For j = 1 To 500    testRange j  Next j  DoEvents  For j = 1 To 500    testRangeSimple j  Next j  DoEvents  For j = 1 To 500    testCellsSimple j  Next j  Application.Calculation = xlCalculationAutomatic  Application.ScreenUpdating = True  For j = 1 To 5    Beep    DoEvents  Next jEnd Sub

I expanded upon the testing after seeing an example of .Cells(1, "A") notation which I thought might be a good balance between the readability of .Range("A1") with the speed of .Cells(1, 1)

I tested reads and writes and found for reads, .Cells(1, "A") executed in about 69% of the time .Range("A1") and .Cells(1, 1) executed in half the time of .Range("A1"). For writes there was a smaller difference (~88% and 82% respectively).

Code:

Option ExplicitSub test()Dim i, x, y, a, t1, t2, t3, t4x=1000000y=x/100Debug.Print "---Read---" 'Cell A1 contains the number 55t1=Timer*1000For i = 1 to x    a = Sheet1.Range("A1")Nextt2=Timer*1000Debug.Print t2 - t1 & "ms"For i = 1 to x    a = Sheet1.Cells(1, "A")Nextt3=Timer*1000Debug.Print t3 - t2 & "ms (" & Round(100*(t3-t2)/(t2-t1),1)&"%)"For i = 1 to x    a = Sheet1.Cells(1, "A")Nextt4=Timer*1000Debug.Print t4 - t3 & "ms (" & Round(100*(t4-t3)/(t2-t1),1)&"%)"Debug.Print "---Write---"    a=55t1=Timer*1000For i = 1 to y    Sheet1.Range("A1") = aNextt2=Timer*1000Debug.Print t2 - t1 & "ms"For i = 1 to y    Sheet1.Cells(1, "A") = aNextt3=Timer*1000Debug.Print t3 - t2 & "ms (" & Round(100*(t3-t2)/(t2-t1),1)&"%)"For i = 1 to y    Sheet1.Cells(1, "A") = aNextt4=Timer*1000Debug.Print t4 - t3 & "ms (" & Round(100*(t4-t3)/(t2-t1),1)&"%)"Debug.Print "----"End Sub

^{^transcribed by hand, may contain typos...}

Platform:
Excel 2013 32 bit
Windows 7 64 bit
16GB Ram
Xeon E5-1650 v2 @3.5GHz

(edit: changed "x" to "y" in write section of code-see disclaimer on hand-typed code!)

It's worth linking this stack overflow question which further explains how to increase performance:

Slow VBA macro writing in cells