ExcelマクロVBA技術解説
VBAのFindメソッドの使い方には注意が必要です

ExcelマクロVBAの問題点と解決策、エクセルVBAの技術的解説
最終更新日:2019-06-29

VBAのFindメソッドの使い方には注意が必要です


VBA Find メソッド


vba find での検索が極めて多く、Findメソッドは検索からの流入ではトップクラスです、
アクセス解析で分かった事ですが正直少し戸惑っています。
なぜなら私はFindメソッドをほとんど使いません
Match関数や配列を使って処理したほうが高速かつ確実に動作するからです。


確かに、Findメソッドについての掲載はあります、こちらです。
マクロVBA入門.第98回.Findメソッド,FindNextメソッド(検索,次を検索)
Findメソッドは、VBAでセル範囲内の条件に当てはまるセルを検索するものです。Findメソッドは、Rangeオブジェクトのメソッドで、ワークシート操作の「検索と置換」の「検索」の機能をVBAで使うものです。上の画像では「検索する文字列」だけしか指定できませんが、「オプション」をクリックすると、
ですが、マクロ入門として解説しているにすぎません。
他のサンプルコードでは、ほとんど使っていません。

なぜ、私がFindメソッドを使わないかと言うと、理由は以下になります。
1.処理速度が遅い
2.指定オプションがシート操作とリンクしている
3.表示形式に依存した検索のため、存在していても検索されない場合がある
簡単に言うと、この3つになります。

一番の理由は、1.処理速度が遅い、これに尽きます。
2.は、並べ替え(Sort)でも同じ事ですが、
Sortは高速で処理されますし、何より有効な代替え手段がありません。
3.については、ある意味致命的です。
事前にわかっていればマクロVBAで表示形式を整える等の対策ができないこともありませんが、
結果として1.をさらに悪化させることにになってしまいます。

検索については他の(Match関数や配列を使う)方法があり、むしろその方が処理速度も速く動作が確実です。

1.処理速度が遅い

以下の2つのプロシージャーでテストします。

※実行時間については、計測環境に大きく左右されますので、あくまで参考数値としてお考えください。



Sub test1()
  Debug.Print Timer
  Dim i As Long
  Dim rng As Range
  For i = 1 To 1000
    Set rng = Range("A:A").Find(What:="a10000", _
                  LookIn:=xlValues, _
                  lookat:=xlWhole, _
                  SearchOrder:=xlByColumns, _
                  MatchByte:=False)
  Next
  Debug.Print Timer
End Sub

Sub test2()
  Debug.Print Timer
  Dim i As Long
  Dim rng As Range
  For i = 1 To 1000
    Set rng = Cells(WorksheetFunction.Match("a10000", Range("A:A"), 0), 1)
  Next
  Debug.Print Timer
End Sub

※どちらもエラー処理は入れていません、単純な処理速度比較の為です。

A列のA1~A10000に、a1,a2,・・・a10000
このようにデータを入れて、上記をテスト実行すると、

test1 : 6.78秒
test2 : 0.42秒


これ程までに差が出ます。
これでも、Findメソッドを使う理由があるでしょうか。
恐らく、市販の書籍やVBA解説サイトで常に高い優先順位で解説しているのかもしれません。

当サイトでは、Findメソッドより先に、WorksheetFunctionを解説しています。
第87回.WorksheetFunction(ワークシート関数を使う)
VBA関数以外に、Excelワークシート関数をマクロVBAで使うことが出来ます、ワークシート関数は、VBA関数よりはるかに多くの関数があるので、ぜひ活用したいところです。。ワークシート関数を使う事で、VBAコードを非常に簡潔に記述することが出来る場合が多いものです。
優先順位は間違いなく、WorksheetFunctionが先であり、
WorksheetFunctionで処理可能なら、これを使うべきだと考えています。

ここで、
FindNextメソッドを使うような、繰り返し処理はどうするのか・・・
こんな声が聞こえてきそうです。

こういう場合には、まずは配列を使います。
以下の2つのプロシージャーでテストします。

Sub test3()
  Debug.Print Timer
  Dim i As Long
  Dim rng As Range
  Dim firstAddress As String
  For i = 1 To 100
    With Range("A:A")
      Set rng = .Find(What:="101", _
              LookIn:=xlValues, _
              lookat:=xlPart, _
              SearchOrder:=xlByColumns, _
              MatchByte:=False)
      If Not rng Is Nothing Then
        firstAddress = rng.Address
        Do
          rng.Interior.Color = vbRed
          Set rng = .FindNext(rng)
          If rng Is Nothing Then
            Exit Do
          End If
          If rng.Address = firstAddress Then
            Exit Do
          End If
        Loop
      End If
    End With
  Next
  Debug.Print Timer
End Sub



Sub test4()
  Debug.Print Timer
  Dim i As Long
  Dim rng As Range
  Dim ix As Long
  Dim ary As Variant
  Dim firstAddress As String
  For i = 1 To 100
    Set rng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
    ary = rng
    For ix = LBound(ary) To UBound(ary)
      If ary(ix, 1) Like "*101*" Then
        rng.Cells(ix, 1).Interior.Color = vbRed
      End If
    Next
  Next
  Debug.Print Timer
End Sub

先ほどと同様に、A列のA1~A10000に、a1,a2,・・・a10000

このようにデータを入れて、上記をテスト実行すると、
test3 : 1.65秒
test4 : 0.86秒

結構な差が出ています。

FindNextメソッドを一生懸命覚えるなら、配列処理を覚えた方がよいですね。
そもそも、VBAの簡単な判断として、
Rangeオブジェクトのメソッドは遅いものだと認識しておいた方が良いでしょう。

参考までに
このような場合にもっと速くしたいのなら、Unionメソッドを使う方法があります。

Sub test5()
  Debug.Print Timer
  Dim i As Long
  Dim rng As Range
  Dim rng2 As Range
  Dim ix As Long
  Dim ary As Variant
  Dim firstAddress As String
  For i = 1 To 100
    Set rng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
    ary = rng
    For ix = LBound(ary) To UBound(ary)
      If ary(ix, 1) Like "*101*" Then
        If rng2 Is Nothing Then
          Set rng2 = rng.Cells(ix, 1)
        Else
          Set rng2 = Union(rng2, rng.Cells(ix, 1))
        End If
      End If
    Next
    rng2.Interior.Color = vbRed
  Next
  Debug.Print Timer
End Sub

上記と同様テストで、
test4 : 0.63秒
若干ではありますが、確実に速くはなります。

書き方は何通りもありますので、いろいろ工夫してみるのも良いでしょう。

2.指定オプションがシート操作とリンクしている

Findの構文
Range.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)

引数の説明
What 検索するデータです。文字列など、セル内のデータに該当する値を指定します。
After セル範囲内のセルの 1 つを指定します。
このセルの次のセルから検索が開始されます。
引数 After で指定するセルは、コードからではなく、通常の画面上で検索を行う場合のアクティブ セルに該当します。
このセルの次から検索が開始されるため、範囲内の他のセルがすべて検索され、このセルに戻るまで、このセル自体は検索されません。
この引数を省略すると、対象セル範囲の左上端のセルが検索の開始点になります。
LookIn 情報の種類を指定します。
xlFormulas:数式
xlValues:値
xlComents:コメント文
LookAt

xlPart:検索テキストの一部を検索します。
xlWhole:検索テキスト全体を検索します。

SearchOrder xlByColumns:列を下方向に検索してから、次の列に移動します。
xlByRows:行を横方向に検索してから、次の行に移動します。
SearchDirection

xlNext:一致する次の値を検索します。
xlPrevious:一致する前の値を検索します。

MatchCase 大文字と小文字を区別するには、True を指定します。既定値は False です。
MatchByte この引数は、2 バイト (全角) 文字の言語サポートが選択またはインストールされている場合にだけ使用できます。
2 バイト文字が 2 バイト文字とだけ一致するようにする場合は、True を指定します。
2 バイト文字が 2 バイト文字だけではなく、対応する 1 バイト文字とも一致するようにする場合は False を指定します。
SearchFormat 検索の書式を指定します。

引数LookIn、LookAt、SearchOrder、およびMatchByteの設定は、このメソッドを使用するたびに保存されます。
次にこのメソッドを使用するときにこれらの引数の指定を省略すると、保存された設定が使用されます。
これらの引数の設定を変更すると、[検索と置換]ダイアログボックスに表示される設定が変わります。
また、[検索と置換]ダイアログボックスで設定を変更すると、保存されている値、つまり引数を省略した場合に使用される値が変わります。
このような設定の変更によって生じる問題を避けるには、メソッドを使用するたびに、これらの引数を明示的に指定します。

この引数の問題により、
マクロを実行するユーザーに誤解を与える可能性があります。
シート操作で「検索」は頻繁に使われる機能なので、
Findメソッドを使うと、ユーザーが[検索と置換]を使うときに余分な負担をかけてしまう可能性があります

3.表示形式に依存した検索のため、存在していても検索されない場合がある

[検索と置換]ダイアログボックスでも同じですが、
この機能は表示形式に依存した検索となっています。

find マクロ vba

上図では、
A,B,C列は、入っている値は全て同じで、列幅と表示形式だけの違いだけです。
A列は、表示形式が標準で全桁表示されている
B列は、表示形式が標準で列幅が狭く#####と表示
C列は、表示形式が「#,##0;[赤]-#,##0」カンマの桁区切り
D,E,F列は、入っている値は全て同じで、表示形式だけの違いです。
D列は、表示形式の日付「*2012/3/14」
E列は、表示形式の日付「2012/3/14」
F列は、表示形式のユーザー定義「yyyy/mm/dd」

Sub test11()
  Dim rng As Range
  Set rng = Range("A:A").Find(What:=1234567, _
                LookIn:=xlValues, _
                lookat:=xlWhole, _
                SearchOrder:=xlByColumns, _
                MatchByte:=False)
  If rng Is Nothing Then
    MsgBox "エラー"
  Else
    MsgBox rng.Address
  End If
End Sub

Range("A:A").Find(What:=1234567,
この部分を変更しつつ確認してみると、
Range("A:A").Find(What:=1234567, → $A$7
Range("B:B").Find(What:=1234567, → エラー
Range("C:C").Find(What:=1234567, → エラー
Range("D:D").Find(What:="2019/1/23", → $D$4
Range("E:E").Find(What:="2019/1/23", → エラー
Range("F:F").Find(What:="2019/1/23", → エラー
Range("D:D").Find(What:="2019/01/23", → エラー
Range("E:E").Find(What:="2019/01/23", → エラー
Range("F:F").Find(What:="2019/01/23", → $F$4

このような結果になります。
数値や日付の場合には、Findメソッドを使うにはかなり注意が必要ですし、
実務では安易には使いづらい機能になります。

また、
先に書いたサンプルのWorksheetFunction.Matchを使う場合、日付の場合には少し工夫が必要です。
セル値でMatch関数を使う場合
WorksheetFunction.Match(Range("H1").Value, Columns(4), 0)
この.Valueを付けてしまうとマッチしません。
定数指定でMatch関数を使う場合
WorksheetFunction.Match("2019/1/23", Columns(4), 0)
このようにデータ型が違っていてはエラーとなりマッチしません。

WorksheetFunction.Match(CDate("2019/1/23"), Columns(4), 0)
日付だからといっても、日付型にしてもエラーとなりマッチしません。

WorksheetFunction.Match(CLng(CDate("2019/1/23")), Columns(4), 0)
日付のシリアル値で検索すると正しくマッチします。

Findメソッドでは文字列の検索においても表示形式は関係します。
例えば、
「@"様"」といった表示形式を指定していると、元の値ではFindは検索されなくなります。

最後に

このように、Findメソッドを使う場合はかなり注意しなければならず、
特段にFindメソッドを使う理由が見当たらない為、私はほとんどFindを使うことはありません。
もちろん、全く使わないというわけではありませんし、
また、Findメソッドを使う事を否定するつもりもありません。
実務でも、シート全体から特定文字を検索するときなどには使う場合もあります。

VBAとしては習得必須メソッドではありますが、実際はそんなに使う機会が多いものではない、
といいますか、そんなに頻繁に使うべきメソッドではないということです。
つまり、
「VBA Find」 で使い方をWeb検索してまで使うようなものではなく、
むしろ、これに時間をかけるくらいなら、
他の方法を模索した方が良いのではかいかと思います。



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

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


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

Applicationを省略できるApplicationのメソッド・プロパティ一覧|VBA技術解説(7月22日)
コレクション(Collection)の並べ替え(Sort)に対応するクラス|VBA技術解説(7月20日)
CSVの読み込み方法(ジャグ配列)|VBAサンプル集(7月15日)
その他のExcel機能(グループ化、重複の削除、オートフィル等)|VBA入門(7月14日)
オートフィルタ退避回復クラスを複数シート対応させるVBAクラス|VBA技術解説(7月6日)
オートフィルタを退避回復するVBAクラス|VBA技術解説(7月6日)
IfステートメントとIIF関数とMax関数の速度比較|VBA技術解説(6月23日)
Withステートメントの実行速度と注意点|VBA技術解説(6月6日)
VBA+SeleniumBasicで検索順位チェッカー(改)|VBA技術解説(6月2日)
マクロでShift_JIS文字コードか判定する|VBA技術解説(6月1日)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
3.RangeとCellsの使い方|ExcelマクロVBA入門
4.変数とデータ型(Dim)|ExcelマクロVBA入門
5.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
6.繰り返し処理(For Next)|ExcelマクロVBA入門
7.マクロって何?VBAって何?|ExcelマクロVBA入門
8.ExcelマクロVBAの基礎を学習する方法|エクセルの神髄
9.ひらがな⇔カタカナの変換|エクセル基本操作
10.セルに文字を入れるとは(Range,Value)|VBA入門



  • >
  • >
  • >
  • VBAのFindメソッドの使い方には注意が必要です

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


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




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