関数を作る_7（VBA研究会議事録_18）

VBA研究会で論じられた内容を記録する議事録の18本目（2023/06/10開催分）です。前回の議事録はコチラになります。

hirocom777.hatenadiary.org

前回まででXLOOKUP関数をVBAのFunctionプロシージャを使って再現してきました。

今回はTEXTBEFOR/TEXTAFTER関数に取り組んでみようと思います。

TEXTBEFOR/TEXTAFTER関数とは

TEXTBEFOR/TEXTAFTER関数とはOffice365で使用できるワークシート関数です。Excel2021では使用できません。簡単に説明すると「文字列の特定の区切り文字よりも前（後ろ）にある文字列を返す」ワークシート関数です。詳細の説明は以下のサイトをご確認ください。

今回のお題

それでは今回のお題です。以下のワークシート関数をVBAのFunctionプロシージャを使って再現してみましょう。

TextBefore(文字列, 区切り文字, 番号, 見つからない場合の値)
TextAfter(文字列, 区切り文字, 番号, 見つからない場合の値)

文字列：単一の値、または単一のセルを示す参照式
区切り文字：検索値を捜査する文字
番号：何番目の区切り文字で区切るか
見つからない場合の値：値、文字列、Bool値などの任意の値

実際のコード

それでは実際のコードです。解決方法としては2種類の方法が考えられました。 InStr(InStrRev)を使用する方法と、Splitを使用する方法です。

InStr(InStrRev)を使用したケース

まずは、InStrRevを使用してTextBeforeする方法です。InStrRev関数は文字列の中から指定した文字列を右側から探して位置を返す関数です。InStr関数の逆ですね。

Function textBefore(ByVal tarText As String, ByVal tarSepStr As String, ByVal tarNo As Long, ByVal エラーの値 As String)
    
    Dim tmpStr As String
    Dim 含有数 As Long
    含有数 = Len(tarText) - Len(Replace(tarText, tarSepStr, ""))
    If 含有数 = 0 Or 含有数 < tarNo Then
        tmpStr = エラーの値
    Else
        '後ろから順に切っていく
        'tarNo番目のところまで切っていくには「含有数 - tarNo + 1」の回数を繰り返す
        Dim i As Long
        tmpStr = tarText
        For i = 1 To 含有数 - tarNo + 1
            tmpStr = Left(tmpStr, InStrRev(tmpStr, tarSepStr) - 1)
        Next i
    End If
    
    textBefore = tmpStr
End Function

対象文字列と、対象文字列から区切り文字を削除した文字列の長さの差から、区切り文字の数を割り出しています。次に区切り文字の数が、指定した番号より1少なくなるまで文字を削除します。

続いて、InStrを使用してTextAfterする方法です。

Function textAfter(ByVal tarText As String, ByVal tarSepStr As String, ByVal tarNo As Long, ByVal エラーの値 As String)
    
    Dim tmpStr As String
    Dim 含有数 As Long
    含有数 = Len(tarText) - Len(Replace(tarText, tarSepStr, ""))
    If 含有数 = 0 Or 含有数 < tarNo Then
        tmpStr = エラーの値
    Else
        '前から順に切っていく
        'tarNo目の区切り文字で区切るにはtarNo回実行
        Dim i As Long
        tmpStr = tarText
        For i = 1 To tarNo
            tmpStr = Mid(tmpStr, InStr(tmpStr, tarSepStr) + 1)
        Next i
    End If
    
    textAfter = tmpStr
End Function

おおむね同じ内容になっているのですが、取り出しにMid関数を使っているところが違います。

Splitを使用したケース

Splitを使ったケースも見ていきましょう。こちらはTextBeforeとTextAfterを1つにまとめています。

Function SliceText(ByVal targetCell As Range, _
                    ByVal deliminator As Variant, _
                    ByVal number As Long, _
                    ByVal beforAfter As Boolean, _
                    Optional ByVal 区切り文字無き時 As String = "指定の区切り文字がありませぬ") As String

    '区切り文字無いときの判定
    If InStr(targetCell.Text, deliminator) = 0 Then
        SliceText = 区切り文字無き時
        Exit Function
    End If
    
    Dim arr As Variant
    arr = Split(targetCell.Text, deliminator)
    
    '前半を返すか後半を返すかの分岐
    Dim ret As String
    Select Case beforAfter
        Case True 'TextBefore
            ret = TextSlicer(arr, 0, number - 1, deliminator)
        Case False 'TextAfter
            ret = TextSlicer(arr, number, UBound(arr), deliminator)
    End Select
    
    SliceText = ret
End Function

Splitを使って配列に変換した後に、必要な配列要素を区切り文字で繋げて返します。考え方がシンプルですね。配列の取り出しと区切り文字で繋げる処理は、以下のTextSlicerで実行しています。

Function TextSlicer(ByVal arr As Variant, ByVal 開始Index As Long, ByVal 終了Index As Long, ByVal deliminator As String)
'   配列arrのインデックス番号 "開始Index"～"終了Index"　の要素を引数 deliminator で結合した文字列を返す
    Dim joinedStr As Variant
    
    Dim i As Long
    For i = 開始Index To 終了Index
        joinedStr = joinedStr & deliminator & arr(i)
    Next
    
    TextSlicer = Mid(joinedStr, 2)
End Function

開始位置と終了位置を指定して区切り文字で繋げて返しています。こちらもシンプルで分かりやすいです。