セルの検索や置換を正規表現で行うには

Excelの標準の検索機能はワイルドカードでの検索は可能ですが、正規表現での検索を行うことはできません

無いものはしょうがないので作るしかありません。

で、正規表現での検索と置換になりますので、VBScriptのRegExpクラスを利用することになります。


RegExpクラスを使うための事前設定

正規表現を利用するにはVBScriptのRegExpクラスを利用します。CreateObject関数で書くこともできますが、参照設定をした方が便利です。

正規表現の参照設定の方法は次の通りです。

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

これで設定は終わりです。


正規表現でのセル検索と置換を行う関数

以下のFindCellRegExp関数は指定されたシートにあるセルの中から、正規表現の検索パターンに一致するセルを検索します。

見つかった場合はそのセルを選択します。見つからなかった場合はメッセージを出力します。

引数が5つあり、1.検索対象ワークシート、2.検索パターン、3.大文字小文字の区別設定、4.検索と置換のどちらであるか、5.置換文字列、を指定します。

4番目と5番目は省略可になっており、省略時は検索のみを行い置換は行いません。



コード説明

処理内容はほとんどはコメントに書いてあるので、補足が必要な点について説明します。

この関数がやりたいことは、アクティブセルを起点として、右下に向かって検索を行い見つかればそのセルを検索一致とみなします。右下側にない場合はシートに入力されている一番左上のセルから検索して最初に見つかったセルを検索一致セルとみなします。ただ、コードはそのようには書いておらず、UsedRangeプロパティを使って入力されているセル範囲を左上から右下に向かって全セルでの文字列検索を行い、あとで選択すべきセルがどれなのかを判定しています。

これには2つ理由があります。

1つはシートの中でどこが検索一致しているかを一覧で見られるようにしたかったためで、イミディエイトウィンドウに検索一致セルのアドレスを出力しています。一覧化しておけば、配列やDictionaryで使ったり、通常のExcelの検索機能のようにフォームで実装した場合にリストボックスなどで選択+ジャンプなどに使えるため、そのように実装しました。ここではフォームは使ってませんけど。

もう1つの理由は、検索処理とセル選択処理と置換処理を分離させたかったためです。分離させておけば検索処理と置換処理を共用化しやすくなります。まあこれは好みの問題もあるので。

で、全セルをループしながら、アクティブセルより上側で見つかったセルと下側で見つかったセルで、下側で見つかったセルがあればそちらを選択し、なければ上側のセルを選択し、どちらもなければメッセージを出すようにしています。

あとは、引数で置換を行う指定がされている場合は、検索で見つかったセルに対して文字列の置換を行っています。

使い方

上の関数の使い方は引数に渡して実行するだけです。

以下のコードでは検索時に引数を省略して書く方法と、置換時の方法を書いています。



ブックの全シートを対象にするには

このマクロは指定されたシートを対象にしていますが、ブックの全シートに対して正規表現での検索や置換を行いたい場合があります。

その場合は、この関数内で行っているセルの選択処理は削除した方がよいでしょう。というのも、次に検索一致になるのが次のシートの場合があるためです。シートをまたがった処理を行うためには、セルの選択はブック全体の検索が終わったあとに行うように変更し、rPre変数とrFind変数は関数を抜けたあとも保持できるような仕組みにするのが望ましいでしょう。

気が向いたら、追記もしくは別で紹介するかも、しないかも、です。