正则表达式在VBA中使用

发表于 更新于 分类于

推荐阅读

正则表达式30分钟入门教程：http://deerchao.net/tutorials/regex/regex.htm

如何绑定

  1. 手动引用(前期绑定)<–推荐方法    点击VBE编辑器菜单：工具 - 引用，选取: Microsoft VBScript Regular Expressions 5.5
    1
    Dim regex As New InternetExplorer
  2. 代码引用(后期绑定)
    1
    2
    Dim regex As Object
    Set regex = CreateObject("VBScript.RegExp")

属性

1) Global 属性

False，如果找到匹配的字符，就停止搜索（默认值）；True ，搜索字符串中全部字符

1
2
3
4
5
6
7
8
9
10
11
12
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不分

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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

1
2
3
4
5
6
7
8
9
10
11
12
Sub r_3() 
    Dim regex As Object
    Dim x As String
    x = "a1b2" &amp; Chr(13) &amp; "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 对象。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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 &amp; match
        Next
    End With
    MsgBox y      'y返回123
End Sub

6) Test 方法

返回一个布尔值，该值指示正则表达式是否与字符串成功匹配。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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 &amp; Mid(x, i, 1)
        Next i
    End With
    MsgBox y      'y返回123
End Sub