文字列の抽出と計算　３．VBA：自分だけの関数を作る ④

はじめに
今回のテーマ
ユーザー定義関数内で正規表現を活用
おわりに
- メリット　：
- デメリット：

はじめに

前回はユーザー定義関数で、引数を追加して、どのグループを抽出するか選択できるようにしました。

infoment.hatenablog.com

今回も、前回の内容を改良してみましょう。

今回のテーマ

前回と同じテーマです。
一つのセルに、３桁の数字が３つあります。これらは、半角スペースで区切られています。この数字について、真ん中のグループの和を求めてみましょう。
f:id:Infoment:20180619063141p:plain

ユーザー定義関数内で正規表現を活用

前回までのユーザー定義関数は、規則正しく入力されている場合にのみ有効でした。それ以前に取り扱った正規表現では、様々な「例外」に対応していました。そこで今回は、両者の良いとこ取りをしてみましょう。

Function Calc2(r As Range, myIndex As Long) As Long

    Dim str As String
    Dim myReg As New RegExp
    Dim matchCase As MatchCollection
    
' 正規表現のパターン定義
    myReg.Pattern = "(\d+)\D?"
    
' 正規表現の検索条件　※ひとつ見つけても検索を止めない
    myReg.Global = True

' 半角と全角が混在するため、一旦全て半角化
        str = StrConv(r.Value, vbNarrow)

' パターンにマッチする場合、True が返る
        If myReg.test(str) = True Then
        
' パターンマッチした結果を、マッチコレクションに格納する
            Set matchCase = myReg.Execute(str)

' 指定したグループの数を、Calc2に返す。
            If matchCase.Count >= myIndex Then
                Calc2 = matchCase(myIndex).SubMatches(0)
            Else
                Calc2 = 0
            End If
        End If

End Function

前回の正規表現を用いたマクロから、数値を抜き出す部分だけを関数にしています。このように、特定の機能でプロシージャを分割するのは、とても有効な手段です。なぜなら機能ごとに分割しておけば、レゴブロックのように組み合わせを変えるだけで、別のマクロを容易に作成できるようになるからです。

それでは、Function 部分は繰り返しになりますが、正規表現のみだったプロシージャを二つに分割してみましょう。

Sub myCalc()

    Dim i As Long
    Dim iMax As Long
    
' 最終行番号の取得
    iMax = Cells(Rows.Count, 1).End(xlUp).Row - 1

' 合計セルの初期化
    Cells(iMax + 1, 2) = 0

' 各行の値抽出と足し算
    For i = 2 To iMax
        Cells(iMax + 1, 2) = Cells(iMax + 1, 2) + Calc2(Cells(i, 1), 1)
    Next

End Sub

Function Calc2(r As Range, myIndex As Long) As Long

    Dim str As String
    Dim myReg As New RegExp
    Dim matchCase As MatchCollection
    
' 正規表現のパターン定義
    myReg.Pattern = "(\d+)\D?"
    
' 正規表現の検索条件　※ひとつ見つけても検索を止めない
    myReg.Global = True

' 半角と全角が混在するため、一旦全て半角化
        str = StrConv(r.Value, vbNarrow)

' パターンにマッチする場合、True が返る
        If myReg.test(str) = True Then
        
' パターンマッチした結果を、マッチコレクションに格納する
            Set matchCase = myReg.Execute(str)

' 指定したグループの数を、Calc2に返す。
            If matchCase.Count >= myIndex Then
                Calc2 = matchCase(myIndex).SubMatches(0)
            Else
                Calc2 = 0
            End If
        End If

End Function