Excelのワークシート関数で正規表現を使う

はじめに

皆さんは「Excelのワークシートで正規表現が使えたら良いのに・・・」と思ったことはないでしょうか？
ご存知の通り、正規表現を使うと、ワイルドカードなどとは比較にならない柔軟さで、条件に一致する文字列を指定することができるようになります。
この記事では、文字列抽出の条件に正規表現を使用できるユーザ定義関数を作成する方法を紹介します。

設計方針

Excelには、正規表現の関数が無いので、VBAとVBScriptのRegExp関数を組み合わせることで、ユーザ定義関数を作成します。

作成する関数は、ワークシート関数として使いやすいように、以下のような動作で作成しました。

• 戻り値は1つだけ。 (複数マッチは無視して、最初にマッチした文字列だけを取り扱う。)
• オプションでサブマッチに対応する。
• サブマッチを使用する場合は、正規表現でグループ化をした上で、第三引数にサブマッチのindexを指定する。
• サブマッチを使用しない場合は、第三引数を省略するか、-1を渡す。
• 文字列がマッチしない場合や実行エラーが発生した場合は#N/Aを返す。
• マッチした文字列が数値として解釈できる場合は数値として返すオプションを用意する。(数値を正しくソートできるようになって便利。)

ちなみにこの関数は、電子回路のピンアサイン表などから、CH番号や属性を表す情報を抽出して、ソートするなどの処理がしたくて作成した経緯があります。

使用例

文字列から数値を分離する例です。

CH○○の○○をサブマッチで抽出 (○○は何桁でもマッチする)

後方一致で"L"又は"H"に一致

実装例

関数名：REGEXP
戻り値：Variant 型　(文字列または数値)

引数

#	引数名	型	説明	デフォルト値
1	Arg_String	String	検索対象の文字列	-
2	Arg_RegExpPattern	String	正規表現パターン	-
3	Arg_SubMatchIndex	Integer	サブマッチを使用する場合に指定 -1: サブマッチ無効 0以上: Indexに対応するサブマッチを返す	-1(サブマッチ無効)
4	Arg_ConvertNumric	Boolean	戻り値の数値変換を試みるかどうか。 True:有効 False:無効	True

数値変換機能については自分の使用用途的に有効のほうが使い勝手が良いのでデフォルト有効としています。

VBAのエディタで標準モジュールを作成し、以下のコードをコピペするだけで"REGEXP"関数が使えるようになります。

Option Explicit

Public Function REGEXP(Arg_String As String, _
                       Arg_RegExpPattern As String, _
              Optional Arg_SubMatchIndex As Integer = -1, _
              Optional Arg_ConvertNumric As Boolean = True) As Variant
       
    'Dim VBS_RE As VBScript_RegExp_55.REGEXP
    'Dim RE_Matches As VBScript_RegExp_55.MatchCollection
    'Dim RE_Match As VBScript_RegExp_55.Match
    
    Dim VBS_RE As Object     'VBScript_RegExp_55.REGEXP
    Dim RE_Matches As Object 'VBScript_RegExp_55.MatchCollection
    Dim RE_Match As Object   'VBScript_RegExp_55.Match
    Dim Temporary As Variant '型変換前の結果を格納する

    On Error GoTo REGEXP_Err

    '正規表現による検索を実行
    Set VBS_RE = CreateObject("VBScript.RegExp")
    VBS_RE.Pattern = Arg_RegExpPattern
    Set RE_Matches = VBS_RE.Execute(Arg_String)
    
    If RE_Matches.Count <= 0 Then
        '何もマッチしない場合 #N/Aエラーを返す
        REGEXP = CVErr(xlErrNA)

    Else
        '複数マッチしたかに関わらず常に最初にマッチした結果を使用する。
        'サブマッチが無効の場合はマッチした文字列全体を返す。
        'サブマッチを使用する場合は指定のIndexのサブマッチを返す。
        Set RE_Match = RE_Matches.Item(0)

        If Arg_SubMatchIndex < 0 Then
            Temporary = RE_Match.Value
        Else
            Temporary = RE_Match.SubMatches(Arg_SubMatchIndex)
        End If
        
        '数値変換が有効の場合は数値変換を試みる。
        If Arg_ConvertNumric Then
            REGEXP = TryConvertToNumric(Temporary)
        Else
            REGEXP = Temporary
        End If
            
    End If
    
    Set Temporary = Nothing
    Set RE_Match = Nothing
    Set RE_Matches = Nothing
    Set VBS_RE = Nothing
    Exit Function

REGEXP_Err: 'エラー発生時
    REGEXP = CVErr(xlErrNA)
    Set Temporary = Nothing
    Set RE_Match = Nothing
    Set RE_Matches = Nothing
    Set VBS_RE = Nothing
End Function

Private Function TryConvertToNumric(Arg_Variable As Variant) As Variant
    '数値に変換可能な場合は数値型を返す。
    'そうでない場合はそのままの値を返す。
    If IsNumeric(Arg_Variable) Then
       TryConvertToNumric = Val(Arg_Variable)
    Else
        TryConvertToNumric = Arg_Variable
    End If
End Function

補足

• 正規表現は使用するエンジンにより、サポートする表現や挙動が異なる場合があります。
  意図した結果が得られない時は、VBScriptの正規表現のリファレンスも確認してみてください。
• ワークシートに関係なく使用したい場合は、作成したファイルをアドイン形式(.xlam)のファイルとして保存し、「開発」タブの「Excelアドイン」で指定することで、個別のブックにマクロを埋め込まなくても、使えるようになります。
  (当然、アドインを持っていない人の環境ではエラーになるので、人に渡すファイルで使う場合は注意が必要です。)

改造したい人向け

実装例では、参照設定を追加しなくても動作するように、遅延バインディングを使って記述していますが、この方法ではインテリセンスや、各種チェック機能など、エディタの支援機能が動作しなくなる弊害があります。
下記の２つの設定をすることで、支援機能が動作するようになります。

参照設定の追加

参照設定で「Microsoft VBScript Regular Expressions 5.5」 を追加してください。

オブジェクトの型を明示的に宣言する

先頭のDim文を下記のようにObject型の宣言から明示的な型の宣言に書き換えてください。

    Dim VBS_RE As VBScript_RegExp_55.REGEXP
    Dim RE_Matches As VBScript_RegExp_55.MatchCollection
    Dim RE_Match As VBScript_RegExp_55.Match    

#参考記事

リファレンス

RegExp オブジェクト
https://msdn.microsoft.com/ja-jp/library/cc392403.aspx

正規表現の構文
https://msdn.microsoft.com/ja-jp/library/cc392020.aspx

IsNumeric 関数(VBA)
https://docs.microsoft.com/ja-jp/office/vba/language/reference/user-interface-help/isnumeric-function

Val 関数(VBA)
https://docs.microsoft.com/ja-jp/office/vba/language/reference/user-interface-help/val-function

正規表現について

サルにもわかる正規表現
https://www.mnet.ne.jp/~nakama/

エディタで学ぶ正規表現入門(ドットインストール)
https://dotinstall.com/lessons/basic_regexp_v2/46001