Excel/VBAでFor Eachループが速いらしいので速度比較してみた

2017/05/10 2017/07/06

前回、シート内の検索でループ文で探すより、Match関数を組み合わせて使った方が検索速度が格段に速くなることを紹介しました。※前回の例ではDo～Loop。

仕事でExcelマクロはよく使っています。そんな中、一番苦労するのが、シート内の膨大なデータから特定の文字列を検索するときです。例えばデータ数が１万行もあれば、検索にすごく時間がかかってしまいます。でも、仕事ではシート内に列挙された何千行、何万行のデータ行から特定のキーワードを検索するのって、Excelマクロで一番使われるんじゃないでしょうか。データ量が多いほど時間がかかります。１万行もあったりすると結構しんどいです。今回はこれを超高速にする方法を紹介します。まずは一般的なやり方まず、下の図は郵便番号...

このブログをGoogle+で紹介したところ、知らない外人から突っ込みが入りました。

No…. the best tool for do this operation is the for each sentence, looking for cells in the worksheet.

セルを探すにはfor eachが”ベストツール”なんだそうです。そういや、VBAでfor eachって使ったことないですね。

[ad#top-1]

1 なので、試してみた
2 作ったプログラムはこんな感じ
3 速度測定結果

なので、試してみた

前回と同じく、124155のデータから探すプログラムです。

前回は、Do～Loopで探すと16.5秒だったものが、Match関数を併用することで0.04秒と爆速になりました。そこを突っ込んできたのだから、For Eachはさぞかし速いんでしょうな～（￣ー￣）

作ったプログラムはこんな感じ

以下がFor Eachを使ったプログラムです。

Sub 検索3()
  t1 = Timer()

  For r = 4 To 10
    For Each c In Range("A14:A124128")
      If c = Cells(r, 1) Then
        r2 = c.Row
        Cells(r, 2) = Cells(r2, 2)
        Cells(r, 3) = Cells(r2, 3)
        Cells(r, 4) = Cells(r2, 4)
        Exit For
      End If
      r2 = r2 + 1
    Next c
  Next r

  t2 = Timer()
  Range("C1") = t2 - t1
End Sub

ポイントは5行目の以下の構文です。

For Each c In Range(“A14:A124128”)

Rangeで示した範囲を順繰り変数cに格納してくれます。