あいまい検索を行うには正規表現を使う

セルの文字列の中で、指定した文字があれば色を付けるマクロを以下で紹介しています。

セルの文字列の指定部分に色を付ける

上のリンク先のページではセルの文字列の中から指定文字列があるかどうかをInStr関数で行っていますが、このページではあいまいな検索を行う場合にも対応できるように、正規表現で検索する方法を紹介します。

例えば以下のような住所の一覧がセルに入力されているとします。

福岡県福岡市南区
福岡県福岡市東区
福岡県北九州市小倉北区
福岡県北九州市小倉南区
福岡県柳川市
福岡県粕屋郡新宮町
佐賀県鳥栖市
佐賀県吉野ヶ里町

このときの色付けの条件は、“○○県”で始まり”町”で終わる文字列を赤色“○○県”で始まり”市”で終わる文字列を青色にします。これらの条件に合う文字列を正規表現を使わずに検索させるには、全ての県と町と市の組み合わせを検索条件として準備しなければなりませんが、正規表現を使えばあいまい検索が可能になります。

変更前と変更後で以下のようにしたい場合です。




正規表現を利用するための事前設定

正規表現を利用するにはVBScriptのRegExpクラスを利用します。

以下の手順で参照設定を追加することで利用できるようになります。

  1. VBAの画面でツールメニュー→参照設定 を選択します。
  2. 参照設定ダイアログで「Microsoft VBScript Regular Expressions 5.5」にチェックを付けます。
  3. OKボタンを押して閉じます。




正規表現で一致したセル文字に色を付ける関数

以下の関数は、セルの文字列の中から正規表現を使って文字列を検索し、一致した箇所に指定した文字色を付ける関数です。

引数として3つ持たせており、先頭から順に、「セル選択範囲のRangeオブジェクト」「検索する文字列の正規表現」「一致した文字に付ける色」になります。

 

処理自体はコメントの通りですが、RegExpクラスの使い方にはルールがあり、「1. 条件設定を行い」、「2. Executeメソッドを実行し」、「3. MatchCollectionの中から一致箇所を抽出する」、という流れに従う必要があります。

セルの一部の文字色を変更するのはRangeオブジェクトのCharactersプロパティです。

Charactersプロパティの詳細については「セルの文字列を1文字単位で装飾する」をご参照ください。


使い方

上のSetPartColorRegExp関数の使い方を説明します。

引数の3つの「セル選択範囲のRangeオブジェクト」「検索する文字列の正規表現」「一致した文字に付ける色」を設定することで利用できます。

3番目の引数にはRGB関数を使っていますが、Font.Colorプロパティに設定可能なLong型の値やVBAの色定数でも構いません。

なお、色については「VBAでWebカラーCSS3の147色を指定する」で紹介しています。

実行後は、上で紹介した変更後の状態になります。


注意点

SetPartColorRegExp関数を使う際の注意点があります。

それは色を付ける順番はSetPartColorRegExp関数の呼び出し順に従う点です。

例えば以下のように”○○県”から”区”までを緑色にしたい条件を追加するとします。

 

このコードを追加する位置によって、結果が変わります。先に「県~区」を緑にして、そのあとに「県~市」を青にすると、左側の絵のように上書きされたようになり、逆であれば右側の絵のようになります。

県~市までの青色を生かすのか、それとも、県~区までの緑を生かすのか、などの重複部分があると、SetPartColorRegExp関数の呼び出し順が後になった方が採用されることになります。

色を付ける条件に一致する文字列が重複している場合は、SetPartColorRegExp関数の呼び出し順序の考慮が必要になります。