自作関数　正規表現を使って文字列付き連番に＋１した値を取得する。続き。

自作関数

f:id:bimori466:20210516060845p:plain

自作関数で-、/、空白の文字列付き連番を作る。

前回の記事で「文字列 ＋ 数字」の文字列付き連番の取得方法について解説しました。
bimori466-1.hatenablog.com


今回は、「数字 ＋ 英数字 ＋ 数字」のような場合の文字列付き連番の取得方法について解説します。


１　閲覧対象者

文字列付き連番に＋１した値がほしいとお困りの方。

２　得られる効果

文字列付き連番に対して＋１した値を取得します。

（具体的データの例）
1AE-6-051/011277
1AO-6-033 004181
1AE-6-051/ 043090
1AP-5-160/010109
XPAS192-002584
XAPS055-033051
HAPS411-008042
1SA-6-037 /004943
UAPS028-032458
1AO-6-033 004185
2O-5-025/000440
1EA-6-051/ 043087
PDD-5-004/002984
1OA-5-432 002941

３　文字列付き連番の解析

前回のおさらいになりますが、2章に列挙した具体的なデータ例を分析すると末尾6桁が数字である」ということがわかります。
しかし、今回は「数字＋文字列＋数字」というパターンです。この場合前回作った正規表現のパターン「"(\D+)(\d+)"」ではうまく取れません。「1AE-6-051/011277」を例として、実際に実行してみると以下のような結果になります。

f:id:bimori466:20210518165840p:plain

結果として、AE-7が出力されました。当然ですね。文字列を頭から見て、数字以外の文字列は「AE-」、次にくる数字のみの文字列は「６」です。連番文字列が６なので＋１して「AE-7」となります。ではどうすればいいでしょうか？

結論、-、/、空白で区切ればいいのです。
例の「1AE-6-051/011277」で言うと、「1AE-6-051/」と「011277」に分ければよいのです。
ではどうするか、コード解説します。

４　コードの解説

Sub 正規表現2()

    '正規表現の遅延バインディング
    Dim reg As Object: Set reg = CreateObject("VBScript.RegExp")
    
    '正規表現にマッチした文字列を格納するObject
    Dim matchString As Object, subMatchString As Object
    
    Dim targetString As String
    Dim targetStringプラス1 As String
    Dim strS As String
    Dim strN As String
    Dim separationStr As String
    Dim serchSrting As String
    Dim targetLen
    Dim resurutInstr1, resurutInstr2, resurutInstr3, rr
    Dim serchInstr1, serchInstr2, serchInstr3
    
    targetString = "1AE-6-051/011277"
    
    reg.Pattern = "(\D+)(\d+)"
        
    Set matchString = reg.Execute(targetString)(0)
    Set subMatchString = matchString.submatches
    
    
    'Pattern = "(\D+)(\d+)"がOKの処理
    strS = subMatchString(0)
    strN = subMatchString(1)
    
    
    If targetString = subMatchString.Item(0) & subMatchString.Item(1) Then
        targetLen = Len(strN)
        
        '連番作成
        strN = CLng(strN) + 1
        targetStringプラス1 = strS & Format$(strN, String$(targetLen, "0"))
        Debug.Print targetStringプラス1
    
    Else
        If InStr(1, targetString, "/") > 0 Then
                        
            '/より左側の文字列を取得
            reg.Pattern = "([^/]+(?:/))"
            Set matchString = reg.Execute(targetString)(0)
            strS = matchString.Value
            
            '/より右側の文字列を取得（/）を含むので、再度"(\D+)(\d+)"で数値を求める。
            reg.Pattern = "([/](\d+))"
            Set matchString = reg.Execute(targetString)(0)
            strN = matchString.Value
            
            reg.Pattern = "(\D+)(\d+)"
            Set matchString = reg.Execute(strN)(0)
            Set subMatchString = matchString.submatches
            separationStr = subMatchString.Item(0)
            strN = subMatchString.Item(1)
            targetLen = Len(strN)
            
            
            '連番作成
            strN = CLng(strN) + 1
            targetStringプラス1 = strS & Format$(strN, String$(targetLen, "0"))
            Debug.Print targetStringプラス1
        End If
    End If
    
End Sub


１　　変数targetStringに「1AE-6-051/011277」を代入。
２　　正規表現パターン”(\D+)(\d+)”でオブジェクト変数matchStringに文字列を取得する。
３　　オブジェクト変数subMatchStringにオブジェクト変数matchStringのsubmatchesを代入する。
４　　変数strSにオブジェクト変数subMatchString(0)を代入する。
　　　変数strNにオブジェクト変数subMatchString(1)を代入する
５　　変数targetStringと変数strS＆変数strNを比較する。

　ここからが、「/」の場合の処理
６　　一致しない場合、正規表現パターン"([^/]+(?:/))"でオブジェクト変数matchStringに文字列を取得する（/より左側の文字列を取得）。変数strSに値を代入する。
７　　正規表現パターン "([/](\d+))"でオブジェクト変数matchStringに文字列を取得する（/より右側の文字列を取得）。変数strNに値を代入する。
　　　補足：
　　　　VBAの正規表現では「後読み」ができないので、正確には「/を含む右側の文字列」を取得します。そのため、オブジェクト変数matchStringに対して、正規表現パターン"(\D+)(\d+)"で文字列を取得します。すると、「/」と「数字」に分かれた文字列がオブジェクト変数subMatchStringに取得できます。
８　　オブジェクト変数subMatchString(1)の値を変数strNに代入する。
９　　変数strNを＋１する。
１０　変数targetStringプラス1に、変数strS＆変数strNを代入する。



１～１０の工程を踏みます。長ったらしいですがこれで、「数字＋文字列＋数字」の連番を取ることができます。

処理結果↓↓
f:id:bimori466:20210518214641p:plain


1AE-6-051/011277　→　1AE-6-051/011278、ちゃんと連番になっていますね！

５　自作関数、-、/、空白の文字列付き連番のコード

今までのことを少し応用すれば、-、/、空白の文字列付き連番のコードが作成できます！

コードは以下の通り↓↓

Function myRegMachineNoNext(targetString As String)

    '正規表現の遅延バインディング
    Static reg As Object: Set reg = CreateObject("VBScript.RegExp")
    
    '正規表現にマッチした文字列を格納するObject
    Dim matchString As Object, subMatchString As Object
    
    Dim strS As String
    Dim strN As String
    Dim separationStr As String
    Dim serchSrting As String
    Dim targetLen
    Dim resurutInstr1, resurutInstr2, resurutInstr3, rr
    Dim serchInstr1, serchInstr2, serchInstr3
    
    reg.Pattern = "(\D+)(\d+)"
    
    strS = "": strN = "": separationStr = ""
        
        Set matchString = reg.Execute(targetString)(0)
        Set subMatchString = matchString.submatches
        
        
        If targetString = subMatchString.Item(0) & subMatchString.Item(1) Then
            'Pattern = "(\D+)(\d+)"がOKの処理
            strS = subMatchString(0)
            strN = subMatchString(1)
            
            targetLen = Len(strN)
            
            '連番作成
            strN = CLng(strN) + 1
            myRegMachineNoNext = strS & Format$(strN, String$(targetLen, "0"))
            
        Else
            'Pattern = "(\D+)(\d+)"がNGの処理（/、-、△）の処理。
            '-、/、△（空白）でどれが一番後ろの文字にでてくるか調べる。
            resurutInstr1 = 0: resurutInstr2 = 0: resurutInstr3 = 0
            rr = 1
            Do
                serchInstr1 = InStr(rr, targetString, "/")
                If serchInstr1 = 0 Then Exit Do
                resurutInstr1 = serchInstr1
                If serchInstr1 <> 0 Then rr = serchInstr1 + 1
            Loop Until serchInstr1 = 0
            If IsEmpty(resurutInstr1) Then resurutInstr1 = 0
            
            rr = 1
            Do
                serchInstr2 = InStr(rr, targetString, "-")
                If serchInstr2 = 0 Then Exit Do
                resurutInstr2 = serchInstr2
                If serchInstr2 <> 0 Then rr = serchInstr2 + 1
            Loop Until serchInstr2 = 0
            If IsEmpty(resurutInstr2) Then resurutInstr2 = 0
            
            rr = 1
             Do
                serchInstr3 = InStr(rr, targetString, " ")
                If serchInstr3 = 0 Then Exit Do
                resurutInstr3 = serchInstr3
                If serchInstr3 <> 0 Then rr = serchInstr3 + 1
            Loop Until serchInstr3 = 0
            If IsEmpty(resurutInstr3) Then resurutInstr3 = 0
            
            
            'serchSrting判定(一番後ろに出てくる文字)
            If resurutInstr1 > resurutInstr2 Then
                If resurutInstr3 > resurutInstr1 Then
                    serchSrting = " "
                Else
                    serchSrting = "/"
                End If
                
            Else
                If resurutInstr3 > resurutInstr2 Then
                    serchSrting = " "
                Else
                    serchSrting = "-"
                End If
            End If
            
            
            Select Case serchSrting
            
                Case Is = "/"
                    
                    If InStr(1, targetString, "/") > 0 Then
                        
                        '/より左側の文字列を取得
                        reg.Pattern = "([^/]+(?:/))"
                        Set matchString = reg.Execute(targetString)(0)
                        strS = matchString.Value
                        
                        '/より右側の文字列を取得（/）を含むので、再度"(\D+)(\d+)"で数値を求める。
                        reg.Pattern = "([/](\d+))"
                        Set matchString = reg.Execute(targetString)(0)
                        strN = matchString.Value
                        
                        reg.Pattern = "(\D+)(\d+)"
                        Set matchString = reg.Execute(strN)(0)
                        Set subMatchString = matchString.submatches
                        separationStr = subMatchString.Item(0)
                        strN = subMatchString.Item(1)
                        targetLen = Len(strN)
                        
                        
                        '連番作成
                        strN = CLng(strN) + 1
                        myRegMachineNoNext = strS & Format$(strN, String$(targetLen, "0"))
                    End If
                    
                Case Is = " "
                
                    If InStr(1, targetString, " ") > 0 Then
                        '△より左側の文字列を取得
                        reg.Pattern = "([^\s]+(?:\s))"
                        Set matchString = reg.Execute(targetString)(0)
                        strS = matchString.Value
                        
                        '\sより右側の文字列を取得（\s）を含むので、再度"(\D+)(\d+)"で数値を求める。
                        reg.Pattern = "([\s](\d+))"
                        Set matchString = reg.Execute(targetString)(0)
                        strN = matchString.Value
                        
                        reg.Pattern = "(\D+)(\d+)"
                        Set matchString = reg.Execute(strN)(0)
                        Set subMatchString = matchString.submatches
                        separationStr = subMatchString.Item(0)
                        strN = subMatchString.Item(1)
                        targetLen = Len(strN)
                       
                        
                        '連番作成
                        strN = CLng(strN) + 1
                        myRegMachineNoNext = strS & Format$(strN, String$(targetLen, "0"))
                    End If
                    
                Case Is = "-"
                
                    If InStr(1, targetString, "-") > 0 Then
                        
                        '-より左側の文字列を取得
                        reg.Pattern = "([^-]+(?:-))"
                        Set matchString = reg.Execute(targetString)(0)
                        strS = matchString.Value
                        
                        '-より右側の文字列を取得（-）を含むので、再度"(\D+)(\d+)"で数値を求める。
                        reg.Pattern = "([-](\d+))"
                        Set matchString = reg.Execute(targetString)(0)
                        strN = matchString.Value
                        
                        reg.Pattern = "(\D+)(\d+)"
                        Set matchString = reg.Execute(strN)(0)
                        Set subMatchString = matchString.submatches
                        separationStr = subMatchString.Item(0)
                        strN = subMatchString.Item(1)
                        targetLen = Len(strN)
                        
                        
                        '連番作成
                        strN = CLng(strN) + 1
                        myRegMachineNoNext = strS & Format$(strN, String$(targetLen, "0"))
                    End If
                    
                    
            End Select
        End If

End Function


処理を加えた点としては、-（ハイフン）、/（スラッシュ）、” ”（半角空白）の３つの内で一番遅くでできた文字列を正規表現パターンとします。それくらいですね！

ワークシート上の実行結果↓↓

f:id:bimori466:20210518215443p:plain


文字列付き連番が取得できていますね！以上です。

６　感想

実はこの自作関数は完璧ではありません。そう、末尾に-（ハイフン）、/（スラッシュ）、空白がきたら処理できません。空白はRtrimで対応はできますが、完璧な文字列付き連番にするにはもう少し工夫が必要ですね。しかしながら、大方このパターンで私の業務は問題なかったです。

いかがだったでしょうか。正規表現というとLinux、PowerShellなどをイメージしますが、VBAでも割と使えるんですね。
ExcelＶＢＡ奥が深い。しかし、もっとスマートな書き方がある気はしています。


ではでは、この辺で(^^)/~~~