ExcelマクロVBA技術解説 | 速度比較決定版【Range,Cells,Do,For,ForEach】 | Excelマクロの問題点と解決策、エクセルVBAの技術的解説



最終更新日:2013-02-18

速度比較決定版【Range,Cells,Do,For,ForEach】


何度も言っているのですが、


RangeCellsでどっちが早いか、


とか、


DoForFor Eachでどれが早いか


とか、


そもそも、その議論がナンセンスなんです。


以下のコードと結果を見て、各自で判断して下さい。



巷の議論が、


いかに無意味で、実は良く解っていないのだと言う事を、理解してもらいたい。


以下のテストコードでは、極力他の要素を排除するために、


余計なカウントアップ等を避ける為、


A列の10万行100万行のセルに、全て1を入れています。


そして、毎回シートの全セルをクリア後に実行するようにしました。


さらに、タイム計測では、Timerを使用し、

(Timerは、午前0時から経過した秒数を表す単精度浮動小数点数型)


その開始と終了の差を使用しています。


テストでは、10通りのコードを使用しました。

(10という数字に意味はありません、きりが良いので適当に選びました。)


恐らく、早い遅いの議論をする多くの方は、


この10通りの書き方さえ理解していないでしょう。


コードの解説はしません。


議論の必要がないからです。


普通に、Forで、Cellsを使用すればよいのです。


どうしても、処理速度を上げたければ、配列を使う等の工夫をする。


これが、基本、鉄則です。


普通が一番良いのです。


では、コードと実行結果です。

(使用PCは、結構古いです、あしからず)



テストコード(10通り)

Sub test()
  Const lngMax As Long = 100000
  Application.ScreenUpdating = False
  Cells.Clear
  Call test1(lngMax)
  Cells.Clear
  Call test2(lngMax)
  Cells.Clear
  Call test3(lngMax)
  Cells.Clear
  Call test4(lngMax)
  Cells.Clear
  Call test5(lngMax)
  Cells.Clear
  Call test6(lngMax)
  Cells.Clear
  Call test7(lngMax)
  Cells.Clear
  Call test8(lngMax)
  Cells.Clear
  Call test9(lngMax)
  Cells.Clear
  Call test10(lngMax)
  Application.ScreenUpdating = True
End Sub

Sub test1(max As Long)
  Dim sngTime As Single
  sngTime = Timer
  Dim i As Long
  For i = 1 To max
    Range("A" & i).Select
    Selection.Value = 1
  Next i
  Debug.Print "test1:" & Timer - sngTime
End Sub

Sub test2(max As Long)
  Dim sngTime As Single
  sngTime = Timer
  Dim i As Long
  For i = 1 To max
    Range("A" & i) = 1
  Next i
  Debug.Print "test2:" & Timer - sngTime
End Sub

Sub test3(max As Long)
  Dim sngTime As Single
  sngTime = Timer
  Dim i As Long
  For i = 1 To max
    Range("A1").Offset(i) = 1
  Next i
  Debug.Print "test3:" & Timer - sngTime
End Sub

Sub test4(max As Long)
  Dim sngTime As Single
  sngTime = Timer
  Dim i As Long
  For i = 1 To max
    Range("A1").Item(i) = 1
  Next i
  Debug.Print "test4:" & Timer - sngTime
End Sub

Sub test5(max As Long)
  Dim sngTime As Single
  sngTime = Timer
  Dim i As Long
  i = 1
  Do While i <= max
    Cells(i, 1) = 1
    i = i + 1
  Loop
  Debug.Print "test5:" & Timer - sngTime
End Sub

Sub test6(max As Long)
  Dim sngTime As Single
  sngTime = Timer
  Dim i As Long
  For i = 1 To max
    Cells(i, 1) = 1
  Next i
  Debug.Print "test6:" & Timer - sngTime
End Sub

Sub test7(max As Long)
  Dim sngTime As Single
  sngTime = Timer
  Dim i As Long
  For i = 1 To max
    Cells(i, "A") = 1
  Next i
  Debug.Print "test7:" & Timer - sngTime
End Sub

Sub test8(max As Long)
  Dim sngTime As Single
  sngTime = Timer
  Dim rng As Range
  For Each rng In Range(Cells(1, 1), Cells(max, 1))
    rng.Value = 1
  Next rng
  Debug.Print "test8:" & Timer - sngTime
End Sub

Sub test9(max As Long)
  Dim sngTime As Single
  sngTime = Timer
  Dim i As Long
  Dim MyRng As Range
  Set MyRng = Range(Cells(1, 1), Cells(max, 1))
  For i = 1 To max
    MyRng.Item(i) = 1
  Next i
  Debug.Print "test9:" & Timer - sngTime
End Sub

Sub test10(max As Long)
  Dim sngTime As Single
  sngTime = Timer
  Dim i As Long
  Dim MyAry
  ReDim MyAry(1 To max, 1 To 1)
  For i = 1 To max
    MyAry(i, 1) = 1
  Next i
  Range(Cells(1, 1), Cells(max, 1)).Value = MyAry
  Debug.Print "test10:" & Timer - sngTime
End Sub



テスト結果(10万行)

test1:11.76563
test2:5.65625
test3:5.984375
test4:5.671875
test5:4.125
test6:4.109375
test7:4.375
test8:4.046875
test9:4.03125
test10:0.34375



テスト結果(100万行)

test1:119.9844
test2:56.70313
test3:59.95313
test4:56.625
test5:41.20313
test6:41.17188
test7:43.79688
test8:40.45313
test9:40.51563
test10:3.4375






同じテーマ「ExcelマクロVBA技術解説」の記事

エクセルVBAのパフォーマンス・処理速度に関するレポート
VBAのFindメソッドの使い方には注意が必要です
WorksheetFunction.Matchで配列を指定した場合の制限について
マクロVBAの高速化・速度対策の具体的手順と検証
動的2次元配列の次元を入れ替えてシートへ出力(Transpose)
大量データで処理時間がかかる関数の対処方法(WorksheetFunction)
大量データにおける処理方法の速度王決定戦

新着記事 ・・・新着記事一覧を見る

データクレンジングと名寄せ|ExcelマクロVBA技術解説(10月20日)
SUMIFの間違いによるパフォーマンスの低下について|エクセル関数超技(6月17日)
条件式のいろいろな書き方:TrueとFalseの判定とは|ExcelマクロVBA技術解説(6月15日)
空白セルを正しく判定する方法2|ExcelマクロVBA技術解説(5月6日)
フルパスをディレクトリ、ファイル名、拡張子に分ける|ExcelマクロVBA技術解説(4月15日)
テキストボックスの各種イベント|Excelユーザーフォーム入門(4月9日)
フォルダ(サブフォルダも全て)削除する、Optionでファイルのみ削除|ExcelマクロVBAサンプル集(4月4日)
最後の空白(や指定文字)以降の文字を取り出す|エクセル関数超技(3月26日)
先頭の数値、最後の数値を取り出す|エクセル関数超技(3月26日)
Excelファイルを開かずにシート名をチェック|ExcelマクロVBAサンプル集(3月23日)

アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|ExcelマクロVBA入門
2.RangeとCellsの使い方|ExcelマクロVBA入門
3.Range以外の指定方法(Cells,Rows,Columns)|ExcelマクロVBA入門
4.変数とデータ型(Dim)|ExcelマクロVBA入門
5.セルのコピー&値の貼り付け(PasteSpecial)|ExcelマクロVBA入門
6.定数と型宣言文字(Const)|ExcelマクロVBA入門
7.徹底解説(VLOOKUP,MATCH,INDEX,OFFSET)|エクセル関数超技
8.マクロって何?VBAって何?|ExcelマクロVBA入門
9.CSVの読み込み方法|ExcelマクロVBAサンプル集
10.ひらがな⇔カタカナの変換|エクセル基本操作



  • >
  • >
  • >
  • 速度比較決定版【Range,Cells,Do,For,ForEach】

  • このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


    記述には細心の注意をしたつもりですが、
    間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
    なお、掲載のVBAコードは自己責任で使ってください。万一データ破損等の損害が発生しても責任は負いません。

    ↑ PAGE TOP