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