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?
The Cells and Range functions let you tell your VBA script exactly where on your worksheet you want to obtain, or place data. The main difference between the two cells is what they reference. The VBA cells function usually references a single cell at a time, while Range references a group of cells at once.
You use Cells() to refer to Single cells on the worksheet, or All cells on the worksheet. The differences between Range and Cells: Cells refers to Single cells only – it can't refer to multiple cells like Range (“A1:E5”) Cells takes Row and Column as arguments.
In VBA, Range is an object, but Cell is a property in an excel sheet. In VBA, we have two ways of referencing a cell object one through Range, and another one is through Cells. For example, if you want to reference cell C5, you can use two methods to refer to the cell C5.
Range is a property in VBA that helps specify a particular cell, a range of cells, a row, a column, or a three-dimensional range. In the context of the Excel worksheet, the VBA range object includes a single cell or multiple cells spread across various rows and columns.
I have done some testing to see what's what.
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.
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
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.
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.
Did I miss anything? Did I cock something up? Please don't hesitate to point it out! Cheers! :)
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
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!)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With