Range() VS Cells() - run times

Question

I see a lot of VBA code on this site using the Range method with For loops:

Range("A" & i)

As opposed to a proper Cells command:

Cells(i,1)

I always knew the Cells way was faster, partly because Range takes longer to resolve, and partly because concatenation (&) is a relatively slow process (as opposed to any other simple arithmetic operation - AFAIK).

So, the question is, is it really faster? By how much? Sometimes, the Range format is more readable, especially for newbies. Does the speed gain justify the slight discomfort and necessary extra explanation in replies?

score 22 · Accepted Answer · answered Mar 18 '16 at 00:13

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 With
Next 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,8
median     125       125       374       328
mode       125       125       374       328
stdev      4,1       4,7       5,7       5,4
min        109       124       358       327
max        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 Long
Sub 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 - t1
End Sub
Sub 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 - t1
End Sub
Sub 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 - t1
End Sub
Sub 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 - t1
End Sub

Sub 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 j

End Sub

Nice work. Do you mind running another test with something along the line of `Cells(i, "A")`? — Ralph, Mar 18 '16 at 00:19
@Ralph it came up with 180 ms, which is somewhat slower than the original `Cells` solution, but definitely faster than the `Range` method. I'll update my answer tomorrow after rerunning it a few times. — vacip, Mar 18 '16 at 00:32
Can you please compare `Range("A1").Offset(i-1,0)` to `Cells(i,1)` also. The fasters should be a range to array reading. Maybe a better benchmark is setting an 1000×1000 table with values using various methods. — John Alexiou, Apr 25 '17 at 14:04
With my testing of filling tables with values `Range("top").Offset(i,j)` yields 34,800 cells/second, `Cells(i,j)` 35,000 cells/second and memory arrays 1,780,500 cells/second. — John Alexiou, Apr 25 '17 at 14:36
Copying the range to a VBA array is know to be much more faster but that's another story. The Range with Offset is a good remark because it is much more readable than "A" & i... — Marco Guignard, Jan 15 '18 at 14:13

MattD · Answer 2 · 2017-05-23T19:15:30.450

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 Explicit
Sub test()
Dim i, x, y, a, t1, t2, t3, t4
x=1000000
y=x/100
Debug.Print "---Read---" 'Cell A1 contains the number 55
t1=Timer*1000
For i = 1 to x
    a = Sheet1.Range("A1")
Next
t2=Timer*1000
Debug.Print t2 - t1 & "ms"
For i = 1 to x
    a = Sheet1.Cells(1, "A")
Next
t3=Timer*1000
Debug.Print t3 - t2 & "ms (" & Round(100*(t3-t2)/(t2-t1),1)&"%)"
For i = 1 to x
    a = Sheet1.Cells(1, "A")
Next
t4=Timer*1000
Debug.Print t4 - t3 & "ms (" & Round(100*(t4-t3)/(t2-t1),1)&"%)"
Debug.Print "---Write---"    
a=55
t1=Timer*1000
For i = 1 to y
    Sheet1.Range("A1") = a
Next
t2=Timer*1000
Debug.Print t2 - t1 & "ms"
For i = 1 to y
    Sheet1.Cells(1, "A") = a
Next
t3=Timer*1000
Debug.Print t3 - t2 & "ms (" & Round(100*(t3-t2)/(t2-t1),1)&"%)"
For i = 1 to y
    Sheet1.Cells(1, "A") = a
Next
t4=Timer*1000
Debug.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!)

score 0 · Answer 3 · answered Jul 19 '18 at 16:08

0

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

Slow VBA macro writing in cells

answered Jul 19 '18 at 16:08

GisMofx

982
9
27