VBScript.RegExp 正規表示式excel vba 學習經驗

1) 手動引用(前期繫結)
   點選VBE編輯器選單：工具 - 引用，選取:
Microsoft VBScript Regular Expressions 5.5
   Dim regex As
New InternetExplorer

2)
程式碼引用(後期繫結)
   Dim regex As Object
   Set regex =
CreateObject("VBScript.RegExp")


1) Global 屬性
  
False，如果找到匹配的字元，就停止搜尋（預設值）
   True ，搜尋字串中全部字元
    Sub
r_1()
        Dim regex As
Object
        Dim x As String
        x
= "a1b2c3"
        Set regex =
CreateObject("VBScript.RegExp")
        With
regex
            .Global
= True    
'返回"a#b#c#"
'            .Global
=
False    '返回"a#b2c3"
            .Pattern
= "\d"    '數字字元匹配
            MsgBox
.Replace(x, "#")
        End With
    End Sub


2) IgnoreCase 屬性
  
如果搜尋是區分大小寫的，為False（預設值）
   True不分
    Sub
r_2()
        Dim regex As
Object
        Dim x As String
        x
= "a1A2"
        Set regex =
CreateObject("VBScript.RegExp")
        With
regex
            .Global
= True
            .IgnoreCase
= True     '返回"#1#2"
    '        .IgnoreCase
=

False    '返回"ab#2"
            .Pattern
= "A"    '數字字元匹配
            MsgBox
.Replace(x, "#")
        End With
    End Sub

3) Multiline 屬性
  
返回正規表示式是否具有標誌m , 預設值為False
    Sub
r_3()
        Dim regex As
Object
        Dim x As String
        x
= "a1b2" & Chr(13) &
"c3d4"
        Set regex =
CreateObject("VBScript.RegExp")
        With
regex
            .Global
= True
'            .MultiLine
=
True
            .Pattern
=
"\d+$"
            MsgBox
.Replace(x, "#")
        End With
    End Sub

4) Pattern 屬性
  
一個字串，用來定義正規表示式。預設值為空文字。

5) Execute 方法
   返回一個
MatchCollection 物件，該物件包含每個成功匹配的 Match 物件。
   Sub
r_5()
        Dim regex As
Object
        Dim matchs As
Object, match As Object
        Dim x As String, y As
String
        x
= "a1b2c3"
        Set regex =
CreateObject("VBScript.RegExp")
        With
regex
            .Global
= True
            .Pattern
= "\d" '匹配數字
            Set matchs =
.Execute(x)
            For Each match
In
matchs
                y
= y &
match
            Next
        End With
        MsgBox
y    'y返回123
    End Sub

6) Test 方法
  
返回一個布林值，該值指示正規表示式是否與字串成功匹配。
    Sub
r_6()
        Dim regex As
Object
        Dim x As String, y As
String
        Dim i As Integer
        x
= "a1b2c3"
        Set regex =
CreateObject("VBScript.RegExp")
        With
regex
            .Global
= True
            .Pattern
=
"\d"
            For i = 1 To
Len(x)
                If .Test(Mid(x, i, 1)) Then y = y & Mid(x, i,
1)
            Next
i
        End With
        MsgBox
y    'y返回123
    End Sub