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

ExcelマクロVBAの問題点と解決策、VBAの技術的解説
公開日:2013-06-06 最終更新日:2021-08-22

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


VBA Find メソッド


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


確かに、Findメソッドについての掲載はあります、こちらです。
マクロVBA入門.第98回.Findメソッド,FindNextメソッド(検索,次を検索)
・Findメソッド ・FindNext メソッド ・FindPrevioust メソッド ・Application.FindFormatメソッド ・FindメソッドとFindNextメソッドの実戦例
ですが、マクロ入門として解説しているにすぎません。
他のサンプルコードでは、ほとんど使っていません。

なぜ、私がFindメソッドを使わないかと言うと、理由は以下になります。

1.処理速度が遅い
2.指定オプションがシート操作とリンクしている
3.「値」で検索した場合は、表示形式に依存した検索になる

簡単に言うと、この3つになります。

一番の理由は、1.処理速度が遅い、これに尽きます。

2.は、並べ替え(Sort)でも同じ事ですが、
Sortは高速で処理されますし、何より有効な代替え手段がありません。

3.については、LookIn:とLookAtが関係します。
この仕様を理解するのはかなり大変です。
事前にわかっていれば、しっかりオプションを設定したり、マクロVBAで表示形式を整える等の対策ができますがかなり面倒です。、

検索については他の(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(ワークシート関数を使う)
・ワークシート関数の使い方 ・WorksheetFunctionで使用できる関数 ・個別の関数の使い方 ・関数の結果(戻り値) ・WorksheetFunctionの使用例. ・検索系の関数での日付の扱い ・WorksheetFunctionのエラー対処 ・最後に
優先順位は間違いなく、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」

良くありがちなFindメッソッドの使い方をしたVBAです。

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
このような結果になります。

LookInLookatを工夫することで検索することは出来ますが、
セルの状態に合わせてVBAコードを書くことも難しいですし、VBAを見て理解することも難しいでしょう。
ただし、数式で入っているセルを検索する場合は、xlFormulas:数式で検索しても表示形式に依存する為検索できないものがでてきます。
この場合はxlValues:値で表示形式に合わせた検索が必要になります。

このような理由があるので、数値や日付の場合には、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メソッドの使い方には注意が必要です
マクロVBAの高速化・速度対策の具体的手順と検証
動的2次元配列の次元を入れ替えてシートへ出力(Transpose)
大量データで処理時間がかかる関数の対処方法(SumIf)
大量データにおける処理方法の速度王決定戦
遅い文字列結合を最速処理する方法について
大量VlookupをVBAで高速に処理する方法について
Withステートメントの実行速度と注意点


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

VBA10大躓きポイント(初心者が躓きやすいポイント)|VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成|ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。|エクセル雑感(2024-02-17)
初級脱出10問パック|VBA練習問題(2024-01-24)
累計を求める数式あれこれ|エクセル関数応用(2024-01-22)
複数の文字列を検索して置換するSUBSTITUTE|エクセル入門(2024-01-03)
いくつかの数式の計算中にリソース不足になりました。|エクセル雑感(2023-12-28)
VBAでクリップボードへ文字列を送信・取得する3つの方法|VBA技術解説(2023-12-07)
難しい数式とは何か?|エクセル雑感(2023-12-07)
スピらない スピル数式 スピらせる|エクセル雑感(2023-12-06)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.変数宣言のDimとデータ型|VBA入門
6.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
7.並べ替え(Sort)|VBA入門
8.条件分岐(IF)|VBA入門
9.セルのクリア(Clear,ClearContents)|VBA入門
10.マクロとは?VBAとは?VBAでできること|VBA入門




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


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



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