Excel VBA 正規表現を使う

Excel VBA 正規表現を使う

Excel VBA | テンプレート:Category 正規表現テンプレート:Category VBAソース片

[MSDN Visual Basic Scripting Edition RegExp オブジェクト] |

基本的な使い方

Dim reg     As Object
Dim matches As Object
Dim match   As Object
Dim value   As String '解析する文字列

Set reg = CreateObject("VBScript.RegExp")
reg.Pattern = "^[ ]+([0-9A-Z@]+).*[ ]FOUND[ ].*"
'reg.Global = True '/g オプションを指定する
Set matches = reg.Execute(value)

If matches.Count > 0 Then
    ' 一致情報のコレクション
    For Each match In matches   
        Debug.Print match.FirstIndex
        Debug.Print match.Value
    Next
    
    Set match = matches(0)
    ' 一致グループ
    Debug.Print match.SubMatches(0)
End If

"(...)"に一致する箇所を抜き出して配列に格納

Dim reg       As Object
Dim matches   As Object
Dim match     As Object
Dim indexes() As String
Dim i         As Integer

i = 0
ReDim indexes(i)

Set reg = CreateObject("VBScript.RegExp")
reg.Pattern = "([0-9]{1,3})"
reg.Global  = True

Set matches = reg.Execute(value)
If matches.Count > 0 Then
    ' 一致情報のコレクション
    For Each match In matches
        ReDim Preserve indexes(i)
        indexes(i) = match.SubMatches(0)
        i = i + 1
    Next
End If