VBScript 正規表示式

Set createVBScriptRegExObject = CreateObject("vbscript.RegExp")

工具>參考> Microsoft VBScript 正規表示式#。#
Associated DLL：VBScript.dll
源：Internet Explorer 1.0 和 5.5

• MSDN-Microsoft 利用正規表示式提升 VBScript
• MSDN-正規表示式語法（指令碼）
• expert-exchange - 在 Visual Basic for Applications 和 Visual Basic 中使用正規表示式 6
• 如何在 Microsoft Excel 中使用正規表示式（正規表示式）在單元格和迴圈 SO 上。
• regular-expressions.info/vbscript
• regular-expressions.info/vbscriptexample
• WIKI-正規表示式

碼

你可以使用此函式獲取 RegEx 結果，將所有匹配（如果大於 1）連線成 1 個字串，並在 excel 單元格中顯示結果。

Public Function getRegExResult(ByVal SourceString As String, Optional ByVal RegExPattern As String = "\d+", _
    Optional ByVal isGlobalSearch As Boolean = True, Optional ByVal isCaseSensitive As Boolean = False, Optional ByVal Delimiter As String = ";") As String
    
    Static RegExObject As Object
    If RegExObject Is Nothing Then
        Set RegExObject = createVBScriptRegExObject
    End If
    
    getRegExResult = removeLeadingDelimiter(concatObjectItems(getRegExMatches(RegExObject, SourceString, RegExPattern, isGlobalSearch, isCaseSensitive), Delimiter), Delimiter)
    
End Function

Private Function getRegExMatches(ByRef RegExObj As Object, _
    ByVal SourceString As String, ByVal RegExPattern As String, ByVal isGlobalSearch As Boolean, ByVal isCaseSensitive As Boolean) As Object

    With RegExObj
        .Global = isGlobalSearch
        .IgnoreCase = Not (isCaseSensitive) 'it is more user friendly to use positive meaning of argument, like isCaseSensitive, than to use negative IgnoreCase
        .Pattern = RegExPattern
        Set getRegExMatches = .Execute(SourceString)
    End With
    
End Function

Private Function concatObjectItems(ByRef Obj As Object, Optional ByVal DelimiterCustom As String = ";") As String
    Dim ObjElement As Variant
    For Each ObjElement In Obj
        concatObjectItems = concatObjectItems & DelimiterCustom & ObjElement.Value
    Next
End Function

Public Function removeLeadingDelimiter(ByVal SourceString As String, ByVal Delimiter As String) As String
    If Left$(SourceString, Len(Delimiter)) = Delimiter Then
        removeLeadingDelimiter = Mid$(SourceString, Len(Delimiter) + 1)
    End If
End Function

Private Function createVBScriptRegExObject() As Object
    Set createVBScriptRegExObject = CreateObject("vbscript.RegExp") 'ex.: createVBScriptRegExObject.Pattern
End Function