VBA性能向上委員会

VBAプログラミングの実行速度に関して、より速い性能を求めて徹底検証をします。


HOME > VBA性能徹底検証 > セルの検索(2次元配列)

セルの検索(2次元配列)

前回の1次元配列の検索では、ワークシート関数のVLOOKUP関数が圧倒的に速いことがわかりました。

しかし、セルは行×列で成り立っているので、2次元配列で検証しなければ、本来の検証とは言えません。

2次元配列の検索も、1次元配列のときと同様に、次の4つの方法で検証を行います。

  • セルをループさせる(For)
  • セルをループさせる(For Each)
  • Findメソッドを使用する
  • ワークシート関数(VLookup関数、HLOOKUP関数)を使用する

今回の検証では、250×250の2次元配列で検証をしてみます。
ぜひ1次元配列のときの結果と見比べてみてください。



セルのループ(For) サンプルプログラム

Dim i As Long
Dim j As Long
For i = 1 To 250 Step 1
For j = 1 To 250 Step 1
If Range("検索範囲").Cells(i, j).Value = "検索文字列" Then
' 検索文字列発見
Exit For
End If
Next j
Next i

1次元配列のときと違い、ループが2重に回されています。



セルのループ(For Each) サンプルプログラム

Dim c As Range
For Each c In Range("検索範囲")
If c.Value = "検索文字列" Then
' 検索文字列発見
Exit For
End If
Next c

For Each構文を使用した検索では、1次元配列のときの検索方法と違いはありません。

"検索範囲"の指定を、[A1:C5]のようにセル範囲にするだけで、2次元配列の検索が可能となります。



Findメソッド サンプルプログラム

Dim c As Range
Set c = Range("検索範囲").Find( _
What:="検索文字列", LookIn:=xlValues, LookAt:=xlWhole)
If Not c Is Nothing Then
' 検索文字列発見
End If

Findメソッドを使用した検索でも、For Each構文を使用した検索のときと同様に

範囲の指定を変えるだけで、2次元配列の検索が可能となります。



ワークシート関数(VLOOKUP関数、HLOOKUP関数) サンプルプログラム

Dim i As Long
Dim c As Range
On Error Resume Next
For i = 0 To 249 Step 1
Debug.Print WorksheetFunction.VLOOKUP( _
"検索文字列", Range("検索範囲").Offset(, i), 1, False)
If Err.Number = 0 Then
' 検索文字列を発見
Exit For
End If
Next i
On Error GoTo 0

VLOOKUP関数は、縦方向一列にしか検索を行わないので、Offsetメソッドを使いループさせています。



検証

さて、1次元配列の検索では、VLOOKUP関数が圧倒的でしたが、2次元配列ではどうでしょう。

実行結果は以下の通りです。

回数ForFor EachFindメソッドワークシート関数
1回目1.5780.4530.1250.734
2回目1.5630.4220.0310.485
3回目1.5940.1560.0320.453
4回目1.6250.4840.0160.454
5回目1.5940.4530.0470.078
6回目1.5310.0160.0150.032
7回目1.5930.4060.0320.328
8回目1.5160.1560.0150.203
9回目1.4850.3120.1090.250
10回目1.5630.4060.0620.438
平均1.5640.3260.0480.346
※単位は全て『秒』

結果は歴然です。

『Findメソッドが圧倒的に速い』ことがわかります。


一般的なプログラムでは、ループ回数が多くなればなるほど、実行速度は遅くなります。

For構文や、VLOOKUP関数を使用した検索方法では、行数や列数により実行速度が左右されます。

その点、Findメソッドは左右されません。(むしろ2次元配列の方が速いです。)


余談ですが、VBAプログラマはなぜか自作ロジックを作るのが大好きな人が多い気がします。

JAVAプログラマは自作ではなく、いかに部品を組み合わせるかを重視するように感じます。

VBA内部には優秀な部品が、実はたくさんあります。

大事なのはその優秀な部品がどれかを見極め、それを使いこなすことだと私は思っています。


Cellsプロパティをループさせることは卒業して、Findメソッドを使用するようにしましょう。



1次元配列での検証結果は、こちらを参照ください。

セルの検索(1次元配列)へ