正则表达式在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