Excel 如何使用正则表达式

原文链接：给你的Excel增加正则处理函数，简直如虎添翼

上文的VBS代码容易出错，且re_extract函数没有把每个捕获组展开输出，以下为完善后的代码：

Option Explicit
 
Public Function re_replace(sText As String, pattern As String, repl As String)
    Dim oRegExp As Object
    Set oRegExp = CreateObject("vbscript.regexp")
    With oRegExp
        .Global = True 'True表示匹配所有, False表示仅匹配第一个符合项
        .IgnoreCase = False '区分大小写
        .pattern = pattern
        re_replace = .Replace(sText, repl)
    End With
End Function
 
Public Function re_find(sText As String, pattern As String)
    Dim oRegExp As Object, match As Object, matches As Object
    Set oRegExp = CreateObject("vbscript.regexp")
    With oRegExp
        .Global = True 'True表示匹配所有, False表示仅匹配第一个符合项
        .IgnoreCase = True '不区分大小写
        .pattern = pattern
        Set matches = .Execute(sText)
    End With
    
    Dim d As Object
    Set d = CreateObject("Scripting.Dictionary")
    Dim indexKey As Integer
    indexKey = 1
    For Each match In matches
        d.Add indexKey, match
        indexKey = indexKey + 1
    Next
    re_find = d.Items
End Function
 
 
Public Function re_extract(sText As String, pattern As String)
    Dim oRegExp As Object, match As Object, matches As Object, subMatch As Variant
    Set oRegExp = CreateObject("vbscript.regexp")
    With oRegExp
        .Global = True 'True表示匹配所有, False表示仅匹配第一个符合项
        .IgnoreCase = True '不区分大小写
        .pattern = pattern
        Set matches = .Execute(sText)
    End With
    
    Dim d As Object
    Set d = CreateObject("Scripting.Dictionary")
    Dim indexKey As Integer
    indexKey = 1
    For Each match In matches
        If match.SubMatches.Count > 1 Then
            For Each subMatch In match.SubMatches
                If subMatch <> "" Then
                    d.Add indexKey, subMatch
                    indexKey = indexKey + 1
                End If
            Next
        Else
            d.Add indexKey, match
            indexKey = indexKey + 1
        End If
    Next
    re_extract = d.Items
End Function