ExcelマクロVBA技術解説 | VBAのFindメソッドの使い方には注意が必要です | Excelマクロの問題点と解決策、エクセルVBAの技術的解説



最終更新日:2016-08-21

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


vba find での検索が極めて多く、

Findメソッドは検索からの流入ではトップクラスです、

アクセス解析で分かった事ですが正直少し戸惑っています、

なぜなら私はFindメソッドをほとんど使いません

確かに、Findメソッドについての掲載はあります、こちらです。


マクロVBA入門.第98回.Findメソッド,FindNextメソッド(検索,次を検索)

ですが、マクロ入門として解説しているにすぎません。

他のサンプルコードでは、ほとんど使っていません。

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

1.処理速度が遅い
2.指定オプションがシート操作とリンクしている
(ユーザーに誤解を与える可能性がある)

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

一番の理由は、1.処理速度が遅い、これでしょうね。

2.は、並べ替え(Sort)でも同じ事ですが、

Sortは高速で処理されますし、何より有効な代替え手段がありません。

しかし、Findメソッドは、他の方法があり、むしろその方が処理速度が速いからです。

以下の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で処理可能なら、これを使うべきだと考えています。


ここで、

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秒

若干ではありますが、確実に速くはなります。

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

このように、特にFindを使う理由が見当たらない為、私はほとんどFindを使うことはありません。

もちろん、全く使わないというわけではありませんし、

また、Findを使う事を否定するつもりもありません。

ただ、VBA Find でWeb検索してまで使うようなものではなく、

むしろ、

他の方法を模索した方が良いと言う事をお伝えしたく、この記事を書いた次第です。





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

WorksheetFunction.Matchで配列を指定した場合の制限について
マクロVBAの高速化・速度対策の具体的手順と検証
動的2次元配列の次元を入れ替えてシートへ出力(Transpose)
大量データで処理時間がかかる関数の対処方法(WorksheetFunction)
大量データにおける処理方法の速度王決定戦
遅い文字列結合を最速処理する方法について
配列の使い方について

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

ファイルの一覧取得・削除(File)|Google Apps Script入門(1月24日)
フォルダの一覧取得・作成・削除(Folder)|Google Apps Script入門(1月24日)
フォルダとファイルを扱う(DriveApp)|Google Apps Script入門(1月24日)
スプレッドシートが非常に遅い、高速化するには|Google Apps Script入門(1月17日)
画像のトリミング(PictureFormat,Crop)|ExcelマクロVBAサンプル集(12月27日)
シート保護|Google Apps Script入門(12月24日)
表示の固定|Google Apps Script入門(12月24日)
グラフ|Google Apps Script入門(12月21日)
入力規則|Google Apps Script入門(12月13日)
並べ替え|Google Apps Script入門(12月12日)

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

1.RangeとCellsの使い方|ExcelマクロVBA入門
2.最終行の取得(End,Rows.Count)|ExcelマクロVBA入門
3.徹底解説(VLOOKUP,MATCH,INDEX,OFFSET)|エクセル関数超技
4.Range以外の指定方法(Cells,Rows,Columns)|ExcelマクロVBA入門
5.セルの参照範囲を可変にする(OFFSET,COUNTA,MATCH)|エクセル関数超技
6.セルのコピー&値の貼り付け(PasteSpecial)|ExcelマクロVBA入門
7.ひらがな⇔カタカナの変換|エクセル基本操作
8.CSVの読み込み方法|ExcelマクロVBAサンプル集
9.変数とデータ型(Dim)|ExcelマクロVBA入門
10.VBAのFindメソッドの使い方には注意が必要です|ExcelマクロVBA技術解説



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

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


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

    ↑ PAGE TOP