Range（）VS Cells（） - 运行时间 [英] Range() VS Cells() - run times
问题描述
我在本网站上使用 Range 方法与对于循环看到了很多VBA代码：
范围（A& i）
与正确的单元格命令相反：
细胞（i，1）
我一直都知道细胞方式更快，部分原因是 Range 需要更长的时间来解决，部分原因是连接（& ）是一个相对较慢的过程（相对于任何其他简单的算术运算--AFAIK）。
所以，问题是，它是否真的更快？多少钱？有时候， Range 格式更易于阅读，特别是对于新手来说。速度增益是否证明了轻微的不适和答复中必要的额外解释？
我做了一些测试，看看是什么。
方法
我已经测试了四种场景的速度。每个测试包括一个For循环执行10万次循环。测试的核心是使用with语句来抓住一个单元格。
对于i = 1至100000
与单元格（i，1）
结束
下一步i
四个测试是：
-
单元格，可变单元格 -
With Cells（我，1） -
单元格，单个单元格 -
使用单元格（1，1） -
范围，变量单元格 -
带范围（A& i） -
范围单细胞 -
范围（A1）
我为四个测试用例使用了单独的subs，并使用第五个子来运行它们500次。
对于时间测量，我使用GetTickCount获得毫秒的准确度。
结果
从500个测量结果来看，结果非常一致。 （我已经运行了100次迭代，几乎相同的结果。）
细胞单元格范围
（变量）（单）（变量）（单）
平均124,3 126,4 372,0 329,8
中值125 125 374 328
模式125 125 374 328
stdev 4,1 4,7 5,7 5,4
min 109 124 358 327
最多156 141 390 344
解释
单元格方法比等效的 Range 方法快2.6倍。如果使用了连接，这又增加了10％的执行时间，差距几乎达到3倍。这是一个巨大的差异。另一方面，我们正在谈论平均每个单元格操作0.001 ms VS 0.004 ms的平均值。除非我们在超过2-3万个单元格上运行脚本，否则这并不会产生明显的速度差异。
结论
是的，有一个巨大的速度差异。
不，我不会打扰告诉人们使用Cells方法，除非他们处理大量的单元格。
测试设置
- Win7 64位
- 8 GB RAM
- Intel Core i7-3770 @ 3.40 GHz
- Excel 2013 32位
-
我错过了什么吗？我有东西吗请不要犹豫，指出吧！干杯! ：）
代码
公共声明函数GetTickCount Libkernel32.dll（）As Long
Sub testCells（j As Long）
Dim i As Long
Dim t1 As Long
Dim t2 As Long
t1 = GetTickCount
对于i = 1至100000
带单元格（i，1）
结束
下一步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
对于i = 1至100000
带范围（A& i）
结束
下一步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
对于i = 1至100000
带范围（A1）
结束
下一步i
t2 = GetTickCount
Sheet4.Cells（j，3）= t2 - t1
结束Sub
Sub testCellsSimple（j As Long）
Dim i As Long
Dim t1 As Long
Dim t2 As Long
t1 = GetTickCount
For i = 1到100000
带单元格（1，1）
结束
下一个i
t2 = GetTickCount
Sheet4.Cells（j，4）= t2 - t1
End Sub
Sub runtests（）
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim j As Long
DoEvents
对于j = 1到500
testCells j
下一个j
DoEvents
对于j = 1到500
testRange j
下一步j
DoEvents
对于j = 1到500
testRangeSimple j
下一个j
DoEvents
对于j = 1到500
testCellsSimple j
下一步j
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
对于j = 1到5
Beep
DoEvents
下一个j
End Sub
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?
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
这篇关于Range（）VS Cells（） - 运行时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
