VBA性能向上委員会

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


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

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

VBAといえば、エクセル。エクセルといえば、セル。

『どのセルを操作するか』を判断することは、自動化を目的としたプログラムの性能を上げるために、最も重要視すべき点のひとつであるといえます。


どのセルかを判断するには、『検索』をします。

検索の方法は、大きく分けて、以下の4つの方法があります。

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

それでは、これら4つの方法について、性能の検証をしてみましょう。



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

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

Range.Cellsを、決められた回数分ループする方法です。

VBAプログラムの中でも、最も基本的なロジックのひとつです。

似た構文にDo Loop構文がありますが、やっていることは同じなので、ここでは割愛します。



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

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

Rangeオブジェクトの数だけループする方法です。

配列の要素数だけループし、セルの場合、列行の順でループをします。


例:Range("A1:B2")の場合

 1回目のループ:A1を参照する

 2回目のループ:B1を参照する

 3回目のループ:A2を参照する

 4回目のループ:B2を参照する



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

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

Range.Findメソッドを使用した検索方法です。

セル範囲のうち、Findメソッドの引数に指定した条件のセルを返却します。

検索条件に合致したセルが存在しない場合は、Nothingを返却します。



VLOOKUP関数、HLOOKUP関数 サンプルプログラム

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

ワークシート関数であるVLOOKUP関数またはHLOOKUP関数を使用した検索方法です。

WorksheetFunctionオブジェクトのメンバで、引数は数式のそれと同様です。

ここでは、VLOOKUP関数のサンプルを提示します。



検証

今回の検証は、セル範囲[A:A]に1~65536を入力し、検索する文字は乱数を発生させ、

セルを検索するものとします。それぞれ4つの方法についての実行結果は、以下の通りです。

回数ForFor EachFindメソッドワークシート関数
1回目0.3590.2190.6300.000
2回目0.3910.3750.4700.000
3回目0.2030.3590.0000.160
4回目0.2030.1720.1600.150
5回目0.6100.4220.4700.000
6回目0.0160.4680.4700.000
7回目0.5160.0150.1600.000
8回目0.6410.5160.1600.000
9回目0.4680.1720.6200.000
10回目0.0310.2340.6200.000
平均0.3440.2950.3760.031

※単位は全て『秒』


この結果より、VLOOKUP関数が圧倒的に速いことがわかります。

ただ、1点注意しなければならないのが、VLOOKUP関数の使用条件に気をつけなければいけないことです。

VLOOKUP関数はもともとワークシート関数、つまりVBAのためではなくエクセルのためにある機能です。

あくまで、セルの値を返す関数であり、セルのアドレスを返す関数ではないため、使い勝手を良くするためには一工夫必要です。

しかもデータが昇順で並んでいないと検索が不安定であることより、ある特定の条件のもと効力を発揮する検索方法であると言えます。


さらに、これが1次元配列の場合であるということに注意しなければなりません。

2次元配列の場合は、結果が大きく変わります。

セルは、行×列の2次元配列ですので、2次元配列での検証をする必要があります。


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

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