VBA：正则表达式(2) -批量修改内容

Option Explicit
Option Base 1

Sub RegExpDemoReplace()

    Dim objRegEx As Object      '用于创建正则对象
    
    Dim objMH As Object         '储存Matchs集合
    
    Dim i As Integer, j As Integer, form As String, newform As String, pre As String, ref As String
    
    Set objRegEx = CreateObject("vbscript.regexp")
    
    objRegEx.Pattern = "[^!]([A-Z]{1,3}\d{1,7})"
    
    objRegEx.Global = True      '全局匹配
    
    For i = 1 To Cells(Rows.Count, "B").End(xlUp).Row
    
        form = " " & Cells(i, "B")  '添加一个空格，确保第一个单元格引用可以被正则表达式匹配到。
        
        pre = Cells(i, 1) & "!"     '工作表名字符串
        
        Set objMH = objRegEx.Execute(form)
        
        If objMH.Count > 0 Then
        
            newform = form
            
            For j = 0 To objMH.Count - 1
            
                ref = objMH(j).submatches(0)
                
                newform = Replace(newform, ref, pre & ref)      '替换新内容
                
            Next
            
            Cells(i, "C") = Trim(newform)   '去除前导空格
            
        End If
        
    Next
    
    Set objRegEx = Nothing
    
    Set objMH = Nothing
    
End Sub

Option Explicit
Option Base 1

Sub TestSubMatches()

    Dim regex As Object
    Dim matches As Object
    Dim match As Object
    
    Dim inputString As String
    
    ' 创建正则表达式对象
    Set regex = CreateObject("VBScript.RegExp")
    
    regex.Global = True ' 启用全局匹配模式
    
    ' 设置正则表达式模式，包含两个捕获组
    regex.Pattern = "(\d+)\s+([a-zA-Z]+)" ' 匹配数字、空格、后跟字母
    
    ' 要在其中查找匹配的文本的输入字符串
    inputString = "There are 123 apples and 456 oranges."
    
    ' 查找所有匹配的内容
    Set matches = regex.Execute(inputString)
    
    ' 遍历匹配项并处理捕获组
    For Each match In matches
    
        ' 输出整个匹配项
        Debug.Print "整个匹配项: " & match.Value
        
        ' 输出第一个捕获组
        Debug.Print "捕获组1: " & match.SubMatches(0)
        
        ' 输出第二个捕获组
        Debug.Print "捕获组2: " & match.SubMatches(1)
        
    Next match
    
End Sub

整个匹配项: 123 apples
捕获组1: 123
捕获组2: apples
整个匹配项: 456 oranges
捕获组1: 456
捕获组2: oranges