Excel VBAによるインクリメンタルサーチの実装方法

Excel VBAを使ってインクリメンタルサーチを実装する方法を紹介します。

コンテンツ

1. インクリメンタルサーチとは
2. Excel VBAによるインクリメンタルサーチ機能の構成
3. 検索対象データを準備する
4. 検索対象データの範囲にフィルターと名前を設定する
5. 検索用テキストボックスを設置する
6. 検索マクロを記述する
7. サンプルファイル

インクリメンタルサーチとは

インクリメンタルサーチ（incremental search）とは、検索文字列を入力するたびに検索結果を返してくれる検索の仕組みです。Googleインスタント検索をイメージしてもらうといいかもしれません。この記事は、Excelファイル上の検索対象データ範囲に対して「検索文字列を入力するたびに最新の検索結果が表示される」インターフェースの実装方法を紹介します。サンプルファイルは記事の最後に用意してあります。

Excelにはフィルター機能が備わっていますが、入力文字列を確定させるまで検索結果は判明しません。昨今のリッチなユーザーインターフェースに慣れきってしまった現代っ子にとっては、標準のフィルター機能に少々物足りなさを感じられるのではないでしょうか。本記事は、そんな「検索文字列を変えるたびにフィルターを操作するのは面倒くさい」、「検索結果を確認しながら少しずつ結果を絞り込みたい」といった私を含む全国のものぐささんのニーズに応えるためのものです。

インクリメンタルサーチに関するより詳しい情報は下記を参照ください。
インクリメンタルサーチ - Wikipedia

Excel VBAによるインクリメンタルサーチ機能の構成

インクリメンタルサーチの実装にあたって、以下の機能を利用します。各機能に習熟している必要はありませんが、本記事では機能の詳細には触れませんので、不明な機能や用語についてはググってください。

• 標準のフィルター機能
• 名前定義
• テキストボックス（ActiveXコントロールの有効化が必要）
• Excel VBA（マクロの有効化が必要）

ActiveXコントロールおよびマクロを有効化する方法については下記を参照ください。
Office ドキュメントの ActiveX コントロールを有効または無効にする - Office のサポート
Office ドキュメントのマクロを有効または無効にする - Office のサポート

検索対象データを準備する

検索対象データはなんでも構いません。

本記事では例として住所を使います（サンプルファイルに格納済み）。下記から日本全国の住所データをダウンロードして、シートのA～B列に格納しました。A列が郵便番号、B列が住所です。今回はB列の住所を検索対象とします。
郵便番号データダウンロード - 日本郵便

検索対象データの範囲にフィルターと名前を設定する

検索対象データを格納した範囲（A～B列）に対してフィルターを設定します。

また、後述の検索マクロにて必要なため、データ範囲に対して名前を定義します。今回はデータ範囲 A3:B123826 に対して AddressList という名前を定義しました。

検索用テキストボックスを設置する

リボンインターフェースの[開発]＞[挿入]から[テキストボックス（ActiveX コントロール）]を選択し、シート上にテキストボックスを配置します。続いて[デザインモード]をオンにし、[プロパティ]を開いて[オブジェクト名]を SearchTextBox と設定します（[オブジェクト名]は任意ですが、ここで設定した[オブジェクト名]によって後述の検索マクロのプロシージャ名が決定されます）。

[オブジェクト名]を設定したら、[デザインモード]をオフにします。

このテキストボックスに文字列が入力されるたび、即座に検索結果が更新されるようになります。

検索マクロを記述する

VBEを表示します（[開発]＞[Visual Basic]、または[Alt+F11]コマンド）。

検索対象データが格納されたシート（サンプルファイルでは SheetSample）を開き、以下のモジュール定数・変数を宣言・定義します。

' Module constant
Const C_RANGE_NAME As String = "AddressList"

' Module variable
Private m_search_text   As String
Private m_search_number As Long

次に、以下ふたつのプロシージャを作成します。

• SearchTextBox_KeyUp
• SearchText

SearchTextBox_KeyUp

「検索用テキストボックスに対してなんらかのキー入力がされた」ことを検知し、実行されるイベントプロシージャです。テキストボックスの検索文字列が変更された場合のみ、サブプロシージャ SearchText を呼び出します。

Private Sub SearchTextBox_KeyUp( _
                ByVal KeyCode As MSForms.ReturnInteger, _
                ByVal Shift   As Integer _
            )

    ' Constant
    Const C_PROC_NAME As String = "SheetSample.SearchText"

    ' Run filtering function
    If m_search_text <> SearchTextBox.Value Then
        m_search_text   = SearchTextBox.Value
        m_search_number = m_search_number + 1
        Application.OnTime EarliestTime:=Now(), _
                           Procedure:="'" & C_PROC_NAME & " " & m_search_number & "'"
    End If

End Sub

SearchText

Private Sub SearchText(ByVal i_search_number As Long)

    With ActiveSheet

    If .AutoFilterMode And i_search_number = m_search_number Then
        If Trim(m_search_text) = "" Then
            ' Clear filter
            If .FilterMode Then
                .ShowAllData
            End If
        Else
            ' Refresh filter
            .Range(C_RANGE_NAME).AutoFilter Field:=2, _
                                            Criteria1:="*" & Trim(m_search_text) & "*"
        End If
    End If

    End With

End Sub

以上で完成です。検索テキストボックスに検索文字列を入力して、インクリメンタルサーチの挙動を確認ください。

今回の例では検索対象列をB列の住所に固定していますが、この記事の実装を応用することで任意の列や複数の列に対するインクリメンタルサーチを実装することも可能です。

サンプルファイル

ご利用は各自のご責任でお願いいたします。改変などはご自由にどうぞ。
サンプルファイルをダウンロード（zip形式、1.98MB）