セル参照のRangeとCellsは使い分けをした方がよいが、、
VBAでセルを参照する際にRangeプロパティかCellsプロパティのどちらかを使うことになります。
その際に、Cellsプロパティだけで実装することも、Rangeプロパティだけで実装することも可能です。
しかし、このようにかなり融通は利くのですが、状況に応じて適切に使い分けを行うことで処理速度が変わってきます。
ただ、そんなに変わりません。
単一セルはCells、複数セルはRangeを使うと速い
結論から言うと、単一セルの場合はCellsプロパティを使い、複数セルの場合はRangeを使うようにするのが一番処理速度は速いです。
ただし、劇的に速くなるようなことにはなりません。
各条件ごとに速度の違いについて後述します。
ただし、処理速度が一番速い書き方にこだわる必要はない、というのが私の考えです。
それについても後述します。
RangeとCellsの使い分け
RangeプロパティとCellsプロパティは厳密に用途が異なります。
Rangeプロパティの用途
Rangeプロパティは以下の２つの用途で使います。
・単一セルをA1形式で参照する場合（Range(“A1”)）
・複数セルやセル範囲を参照する場合（A1～B2セルを参照：Range(“A1:B2”)、A～C列を参照：Range(“A:C”)、A1～B2範囲とC3～D4範囲を参照：Range(“A2:B2,C3:D4”)）
Cellsプロパティの用途
・単一セルを行番号と列番号を指定して参照する場合（A2セルを参照：Cells(2, 1)）
単一セルの参照はCellsの方が速い
単一セルを参照する場合は、Cellsプロパティの方が速いです。
その理由ですが、座標の文字列変換があるかないかの違いと思われます。
Cellsプロパティは行番号と列番号を数値で指定するため、数値がそのままシートのセル座標に置き換えて処理が可能ですが、Rangeプロパティは、”A1″という文字列でセル位置を指定するため、英字部分と数字部分に切り分けて、英字から列番号位置を算出して、行番号文字列と列番号も列をそれぞれ数値に変換してセル座標の置き換え、という流れを行っているものと思われます。
この変換処理の分、RangeプロパティはCellsプロパティよりも時間が掛かります。
以下のソースで実測してみました。
A1セルを参照する処理をRangeとCellsのそれぞれ1万回行っています。
計測には高精度時間を算出する関数を利用しています。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
'// 64bit版 Type LARGE_INTEGER LowPart As Long HighPart As Long End Type #If VBA7 And Win64 Then Declare PtrSafe Function QueryPerformanceFrequency Lib "kernel32" (lpFrequency As LARGE_INTEGER) As Long Declare PtrSafe Function QueryPerformanceCounter Lib "kernel32" (lpPerformanceCount As LARGE_INTEGER) As Long '// 32bit版 #Else Declare Function QueryPerformanceFrequency Lib "kernel32" (frequency As Double) As Long Declare Function QueryPerformanceCounter Lib "kernel32" (procTime As Double) As Long #End If |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
'// 引数：Excelブックのフルパスを指定する Function GetMicroSecondEx(frequency As Double) As Double Dim procTime As Double '// 高分解能パフォーマンスカウンタ値（システム起動からの加算値） Dim ret As Double '// 計測結果 '// 計測時刻を0で初期化 GetMicroSecondEx = 0 '// 処理時刻を取得 Call QueryPerformanceCounter(procTime) '// カウンタ値を１秒間のカウント増加数で割り、正確な時刻を算出 GetMicroSecondEx = procTime / frequency End Function |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
'// 引数：Excelブックのフルパスを指定する Sub RangeCellsSelectSpeedTest() Dim rangeStart As Double Dim rangeEnd As Double Dim rangeDiff As Double Dim cellsStart As Double Dim cellsEnd As Double Dim cellsDiff As Double Dim frequency As Double Dim i As Long '// 更新頻度を取得 Call QueryPerformanceFrequency(frequency) '// 処理前の時間を取得 rangeStart = GetMicroSecondEx(frequency) '// 計測対象の処理 For i = 1 To 10000 Range("A1").Select Next '// 処理後の時間を取得 rangeEnd = GetMicroSecondEx(frequency) '// 処理前の時間を取得 cellsStart = GetMicroSecondEx(frequency) '// 計測対象の処理 For i = 1 To 10000 Cells(1, 1).Select Next '// 処理後の時間を取得 cellsEnd = GetMicroSecondEx(frequency) '// 処理前後の差を取得 rangeDiff = rangeEnd - rangeStart cellsDiff = cellsEnd - cellsStart Debug.Print "Range:" & rangeDiff & "秒" Debug.Print "Cells:" & cellsDiff & "秒" End Sub |
実測結果は、Rangeが1.5秒、Cellsが1.44秒と、Cellsの方が若干速いです。
Range:1.50643464864697秒
Cells:1.44152700668201秒
Range(“A” & CStr(iRow))と書くと遅い
例えばRange(“A1”)からRange(“A100000”)までのループ処理で、1から100000の部分をループカウンタで代用することがあります。
こんな書き方ですね。
|
1 2 3 4 5 6 7 8 9 10 |
'// 引数：Excelブックのフルパスを指定する Sub Range1To100000() Dim iRow '// 1から100000までループ For iRow = 1 To 100000 '// 指定セルを選択 Range("A" & CStr(iRow)).Select Next End Sub |
先に書いた内容と同じですが、Rangeの場合は構文解析が行われることにより処理速度が遅くなります。
以下のようにCellsに書き換えた方が処理速度は速くなります。
|
1 2 3 4 5 6 7 8 9 10 |
'// 引数：Excelブックのフルパスを指定する Sub Cells1To100000() Dim iRow '// 1から100000までループ For iRow = 1 To 100000 '// 指定セルを選択 Cells(iRow, 1).Select Next End Sub |
どれぐらい速くなるのか確認してみます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
'// 引数：Excelブックのフルパスを指定する Sub RangeCellsSpeedTest() Dim rangeStart As Double Dim rangeEnd As Double Dim rangeDiff As Double Dim cellsStart As Double Dim cellsEnd As Double Dim cellsDiff As Double Dim frequency As Double '// 更新頻度を取得 Call QueryPerformanceFrequency(frequency) '// 処理前の時間を取得 rangeStart = GetMicroSecondEx(frequency) '// 計測対象の処理 Call Range1To100000 '// 処理後の時間を取得 rangeEnd = GetMicroSecondEx(frequency) '// 処理前の時間を取得 cellsStart = GetMicroSecondEx(frequency) '// 計測対象の処理 Call Cells1To100000 '// 処理後の時間を取得 cellsEnd = GetMicroSecondEx(frequency) '// 処理前後の差を取得 rangeDiff = rangeEnd - rangeStart cellsDiff = cellsEnd - cellsStart Debug.Print "Range:" & rangeDiff & "秒" Debug.Print "Cells:" & cellsDiff & "秒" End Sub |
実行結果はこうなりました。
Range:15.3357434456702秒
Cells:11.3562810394214秒
ループカウンタの100000を10000に変更すると実行結果はこうなりました。
Range:3.32276758423541秒
Cells:2.35987033683341秒
単純には言えませんが、Cellsに比べて1.4倍程度Rangeの方が時間が掛かっています。
Range(“xx”).Offsetは遅い
私もよく使うOffsetですが、Cellsと比べると処理は遅いです。
基点からの指定行列位置を求める必要があるため、直接アドレスを指定するCellsよりも遅くなります。
これも実測してみます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
'// 引数：Excelブックのフルパスを指定する Sub RangeCellsOffsetSpeedTest() Dim rangeStart As Double Dim rangeEnd As Double Dim rangeDiff As Double Dim cellsStart As Double Dim cellsEnd As Double Dim cellsDiff As Double Dim frequency As Double Dim i As Long '// 更新頻度を取得 Call QueryPerformanceFrequency(frequency) '// 処理前の時間を取得 rangeStart = GetMicroSecondEx(frequency) '// 計測対象の処理 For i = 1 To 10000 Range("A1").Offset(i, 0).Select Next '// 処理後の時間を取得 rangeEnd = GetMicroSecondEx(frequency) '// 処理前の時間を取得 cellsStart = GetMicroSecondEx(frequency) '// 計測対象の処理 For i = 1 To 10000 Cells(i, 1).Select Next '// 処理後の時間を取得 cellsEnd = GetMicroSecondEx(frequency) '// 処理前後の差を取得 rangeDiff = rangeEnd - rangeStart cellsDiff = cellsEnd - cellsStart Debug.Print "Range:" & rangeDiff & "秒" Debug.Print "Cells:" & cellsDiff & "秒" End Sub |
実測結果です。
Cellsに比べて1.65倍ほどRangeの方が時間が掛かっています。
Range:3.71202648698818秒
Cells:2.24162556300871秒
でも、単一セルにRangeやOffsetを使っても構わない
処理速度の向上を目的として書いた記事ではありますが、個人的にはRangeとCellsに関して言えば「慣れている書き方」や「好きな書き方」を優先させて処理速度を犠牲にしても構わないと思っています。
この記事で言えば、単一セルやOffsetでRangeは使わずにCellsを使った方が処理速度は速くなります、と書いてますが、単一セルやOffsetでRangeを使っても全然いいと思います。
現に私はCellsの方が速いと分かっていても、マクロを作る際にRange(“A” & CStr(i))やOffsetを多用します。
VBAに限らずあらゆるプログラミング言語で処理速度向上を行う方法がありますが、個人的には処理速度向上は目的に対する優先度としてはそんなに高くありません。
ここで言う目的とは、以下に挙げるようなマクロを作って行う何らかの自動化、を指します。
- １回しか動かさないマクロなんで処理速度とかどうでもいい。
- ループが10回程度なんでRangeでもCellsでもほとんど変わらないし気にもしない。
- Range(“A” & CStr(i))と書く方がA列なのがはっきりして分かりやすい。
- Offsetを使った方が紙の資料と見比べながらマクロを書く際に分かりやすい
などなど、コードを書くときの状況やコードを書いた人の考え方はバラバラです。
その状況によって何が優先されるのかは変わりますので、ガチガチに「処理速度が速い方法で書け！」なんてことを言ってるのは違うと思います。
そして上でも実測結果を挙げましたが、速いといってもある程度、というぐらいのものです。ついでに言えば、数秒程度の差であればパソコンが新しくなるだけで差も小さくなっていきます。
処理速度をより速くするのは、それ自体は良いことですが、それよりも大事なことがある場合はそちらを優先しましょう。