EXCEL VBA 正则表达式 从实例开始.doc

'EXCEL VBA 正则表达式 从实例开始
所有代码都测试过
'
'
'[2011-09-09：
'[2010-08-08：
'数据验证经常出现的情况是，需要验证和处理大量的文本内容，有时候需要查找字符串，有时候要按条件替换，并且这些待处理的问题还是有一定的规律可循的，但是无法通过枚举的方式挨个验证，这个时候就需要正则表达式来帮忙。
'1.正则表达式(Regular Expression)基础
'　　正则表达式是通用的文本搜索和处理方案，它的知识不是VBA独有的，基本上每种语言都内置了正则表达式的功能。正则表达式的基础知识不是这里的重点，需要的朋友可以Google一下，或者参看下面的一些入门教程：
'
'
'http://www.williamlong.info/archives/433.html
'2.VBA中的正则表达式应用
'　　在VBA中使用正则表达式，可以通过下列途径实现：
'创建正则表达式对象:
'前期绑定：在VBA代码编辑器中的"Tools"菜单中，选中"References..."，然后引用Microsoft VBScript Regular Expressions 5.5类库，然后直接定义对象：Dim reg As New RegExp。
'后期绑定：使用CreateObject方法定义对象：CreateObject("VBSCRIPT.REGEXP")。
'前一种方式的优点是可以有编辑器的Intellisense支持?
'RegExp对象的属性:
'Global - 设置或返回一个 Boolean 值，该值指明在整个搜索字符串时模式是全部匹配还是只匹配第一个。如果搜索应用于整个字符串，Global 属性的值应该为 True，否则其值为 False。默认的设置为True。
'Multiline - 返回正则表达式是否具有标志m, 缺省值为False。如果指定的搜索字符串分布在多行，这个属性是要设置为True的。
'IgnoreCase - 设置或返回一个Boolean值，指明模式搜索是否区分大小写。如果搜索是区分大小写的，则 IgnoreCase 属性应该为False；否则应该设为True。缺省值为True。
'Pattern - 设置或返回被搜索的正则表达式模式。 被搜索的正则字符串表达式。它包含各种正则表达式字符。
'RegExp对象的方法:
'Execute - 对指定的字符串执行正则表达式搜索。需要传入要在其上执行正则表达式的文本字符串。正则表达式搜索的设计模式是通过 RegExp对象的Pattern来设置的。Execute方法返回一个Matches集合，其中包含了在string中找到的每一个匹配的Match对 象。如果未找到匹配，Execute将返回空的Matches集合。
'Replace -替换在正则表达式查找中找到的文本?
'Test - 对指定的字符串执行一个正则表达式搜索，并返回一个 Boolean 值指示是否找到匹配的模式。RegExp.Global属性对Test方法没有影响。如果找到了匹配的模式，Test方法返回True；否则返回False。
'MatchCollection对象与Match对象
'匹配到的所有对象放在MatchCollection集合中，这个集合对象只有两个只读属性：
'Count: 匹配到的对象的数目
'Item：集合的又一通用方法，需要传入Index值获取指定的元素。
'一般，可以使用For Each语句枚举集合中的对象。集合中对象的类型是Match。
'Match对象有以下几个只读的属性:
'FirstIndex - 匹配字符串在整个字符串中的位置，值从0开始。
'Length -匹配字符串的长度?
'Value -匹配的字符串?
'SubMatches - 集合，匹配字符串中每个分组的值。作为集合类型，有Count和Item两个属性。
'
'常用的正则表达式主要有以下几种:
'匹配中文字符的正则表达式: [\u4e00-\u9fa5]
'评注：匹配中文还真是个头疼的事，有了这个表达式就好办了
'匹配双字节字符 (包括汉字在内): [^\x00-\xff]
'评注：可以用来计算字符串的长度（一个双字节字符长度计2，ASCII字符计1）
'匹配空白行的正则表达式：\n\s*\r
'评注: 可以用来删除空白行
'匹配HTML标记的正则表达式：<(\S*?)[^>]*>.*?</>|<.*? />
'评注：网上流传的版本太糟糕，上面这个也仅仅能匹配部分，对于复杂的嵌套标记依旧无能为力
'匹配首尾空白字符的正则表达式：^\s*|\s*$
'评注：可以用来删除行首行尾的空白字符(包括空格、制表符、换页符等等)，非常有用的表达式
'匹配Email地址的正则表达式：\w+([-+.]\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*
'评注: 表单验证时很实用
'匹配网址URL的正则表达式：[a-zA-z]+://[^\s]*
'评注：网上流传的版本功能很有限，上面这个基本可以满足需求
'匹配帐号是否合法(字母开头，允许5-16字节，允许字母数字下划线)：^[a-zA-Z][a-zA-Z0-9_]{4,15}$
'评注: 表单验证时很实用
'匹配国内电话号码：\d-\d|\d-\d
'评注：匹配形式如 0511-4405222 或 021-87888822
'匹配腾讯QQ号：[1-9][0-9]{4,}
'评注: 腾讯QQ号从10000开始
'匹配中国邮政编码：[1-9]\d(?!\d)
'评注: 中国邮政编码为6位数字
'匹配身份证：\d|\d
'评注: 中国的身份证为15位或18位
'匹配ip地址：\d+\.\d+\.\d+\.\d+
'评注: 提取ip地址时有用
'匹配特定数字:
'^[1-9]\d*$　//匹配正整数
'^-[1-9]\d*$ //匹配负整数
'^-?[1-9]\d*$　//匹配整数
'^[1-9]\d*|0$　//匹配非负整数（正整数 + 0）
'^-[1-9]\d*|0$　//匹配非正整数（负整数 + 0）
'^[1-9]\d*\.\d*|0\.\d*[1-9]\d*$　//匹配正浮点数
'^-([1-9]\d*\.\d*|0\.\d*[1-9]\d*)$　//匹配负浮点数
'^-?([1-9]\d*\.\d*|0\.\d*[1-9]\d*|0?\.0+|0)$　//匹配浮点数
'^[1-9]\d*\.\d*|0\.\d*[1-9]\d*|0?\.0+|0$　//匹配非负浮点数（正浮点数 + 0）
'^(-([1-9]\d*\.\d*|0\.\d*[1-9]\d*))|0?\.0+|0$　//匹配非正浮点数（负浮点数 + 0）
'评注：处理大量数据时有用，具体应用时注意修正
'匹配特定字符串:
'^[A-Za-z]+$　//匹配由26个英文字母组成的字符串
'^[A-Z]+$　//匹配由26个英文字母的大写组成的字符串
'^[a-z]+$　//匹配由26个英文字母的小写组成的字符串
'^[A-Za-z0-9]+$　//匹配由数字和26个英文字母组成的字符串
'^\w+$　//匹配由数字、26个英文字母或者下划线组成的字符串
'评注: 最基本也是最常用的一些表达式
'
'代码/语法 说明
' 匹配除换行符以外的任意字符 .
'\w 匹配字母或数字或下划线或汉字
'\s 匹配任意的空白符
'\d 匹配数字
'\b 匹配单词的开始或结束
'^ 匹配字符串的开始
'$ 匹配字符串的结束
'
'表2.常用的限定符
'代码/语法 说明
'* 重复零次或更多次
' 重复一次或更多次 +
'Print 重复零次或一次
'{n} 重复n次
'{n,} 重复n次或更多次
'{n,m} 重复n到m次
'
'表3.常用的反义代码
'代码/语法 说明
'\W 匹配任意不是字母，数字，下划线，汉字的字符
'\S 匹配任意不是空白符的字符
'\D 匹配任意非数字的字符
'\B 匹配不是单词开头或结束的位置
'[^x] 匹配除了x以外的任意字符
'[^aeiou] 匹配除了aeiou这几个字母以外的任意字符
'
'表4.常用分组语法
'代码/语法 说明 分类
'(exp) 匹配exp,并捕获文本到自动命名的组里 捕获
'(?<name>exp) 匹配exp,并捕获文本到名称为name的组里，也可以写成(?'name'exp)
'(?:exp) 匹配exp,不捕获匹配的文本，也不给此分组分配组号
'(?=exp) 匹配exp前面的位置 零宽断言
'(?<=exp) 匹配exp后面的位置
'(?!exp) 匹配后面跟的不是exp的位置
'(?<!exp) 匹配前面不是exp的位置
'(?#comment) 这种类型的分组不对正则表达式的处理产生任何影响，用于提供注释让人阅读 注释
'
'表5.懒惰限定符
'代码/语法 说明
'*? 重复任意次，但尽可能少重复
'+? 重复1次或更多次，但尽可能少重复
'?? 重复0次或1次，但尽可能少重复
'{n,m}? 重复n到m次，但尽可能少重复
'{n,}? 重复n次以上，但尽可能少重复
'
'表6.常用的处理选项
'名称 说明
'IgnoreCase(忽略大小写) 匹配时不区分大小写。
'Multiline(多行模式) 更改^和$的含义，使它们分别在任意一行的行首和行尾匹配，而不仅仅在整个字符串的开头和结尾匹配。(在此模式下,$的精确含意是:匹配\n之前的位置以及字符串结束前的位置.)
'Singleline(单行模式) 更改.的含义，使它与每一个字符匹配（包括换行符\n）。
'IgnorePatternWhitespace(忽略空白) 忽略表达式中的非转义空白并启用由#标记的注释。
'ExplicitCapture(显式捕获) 仅捕获已被显式命名的组。
'
'表7.尚未详细讨论的语法
'代码/语法 说明
'\a 报警字符(打印它的效果是电脑嘀一声)
'\b 通常是单词分界位置，但如果在字符类里使用代表退格
'\t 制表符，Tab
'\r 回车
'\v 竖向制表符
'\f 换页符
'\n 换行符
'\e Escape
'\0nn ASCII代码中八进制代码为nn的字符
'\xnn ASCII代码中十六进制代码为nn的字符
'\unnnn Unicode代码中十六进制代码为nnnn的字符
'\cN ASCII控制字符。比如\cC代表Ctrl+C
'\A 字符串开头(类似^，但不受处理多行选项的影响)
'\Z 字符串结尾或行尾(不受处理多行选项的影响)
'\z 字符串结尾(类似$，但不受处理多行选项的影响)
'\G 当前搜索的开头
'\p{name} Unicode中命名为name的字符类，例如\p{IsGreek}
'(?>exp) 贪婪子表达式
'(?<x>-<y>exp) 平衡组
'(?im-nsx:exp) 在子表达式exp中改变处理选项
'(?im-nsx) 为表达式后面的部分改变处理选项
'(?(exp)yes|no) 把exp当作零宽正向先行断言，如果在这个位置能匹配，使用yes作为此组的表达式；否则使用no
'(?(exp)yes) 同上，只是使用空表达式作为no
'(?(name)yes|no) 如果命名为name的组捕获到了内容，使用yes作为表达式；否则使用no
'(?(name)yes) 同上，只是使用空表达式作为no
'
'在VBA中使用正则表达式，可以通过下列途径实现：
'创建正则表达式对象:
'前期绑定：在VBA代码编辑器中的"Tools"菜单中，选中"References..."，然后引用Microsoft VBScript Regular Expressions 5.5类库，然后直接定义对象：Dim reg As New RegExp。
'后期绑定：使用CreateObject方法定义对象：CreateObject("VBSCRIPT.REGEXP")。
'前一种方式的优点是可以有编辑器的Intellisense支持?
'RegExp对象的属性:
'Global - 设置或返回一个 Boolean 值，该值指明在整个搜索字符串时模式是全部匹配还是只匹配第一个。如果搜索应用于整个字符串，Global 属性的值应该为 True，否则其值为 False。默认的设置为True。
'Multiline - 返回正则表达式是否具有标志m, 缺省值为False。如果指定的搜索字符串分布在多行，这个属性是要设置为True的。
'IgnoreCase - 设置或返回一个Boolean值，指明模式搜索是否区分大小写。如果搜索是区分大小写的，则 IgnoreCase 属性应该为False；否则应该设为True。缺省值为True。
'Pattern - 设置或返回被搜索的正则表达式模式。 被搜索的正则字符串表达式。它包含各种正则表达式字符。
'RegExp对象的方法:
'Execute - 对指定的字符串执行正则表达式搜索。需要传入要在其上执行正则表达式的文本字符串。正则表达式搜索的设计模式是通过 RegExp对象的Pattern来设置的。Execute方法返回一个Matches集合，其中包含了在string中找到的每一个匹配的Match对象。如果未找到匹配，Execute将返回空的Matches集合。
'Replace -替换在正则表达式查找中找到的文本?
'Test - 对指定的字符串执行一个正则表达式搜索，并返回一个 Boolean 值指示是否找到匹配的模式。RegExp.Global属性对Test方法没有影响。如果找到了匹配的模式，Test方法返回True；否则返回False。
'MatchCollection对象与Match对象
'匹配到的所有对象放在MatchCollection集合中，这个集合对象只有两个只读属性：
'Count: 匹配到的对象的数目
'Item：集合的又一通用方法，需要传入Index值获取指定的元素。
'一般，可以使用For Each语句枚举集合中的对象。集合中对象的类型是Match。
'Match对象有以下几个只读的属性:
'FirstIndex - 匹配字符串在整个字符串中的位置，值从0开始。
'Length -匹配字符串的长度?
'Value -匹配的字符串?
'SubMatches - 集合，匹配字符串中每个分组的值。作为集合类型，有Count和Item两个属性。
'
'正则表达式很强大，是处理文本的首选，不管你信不信，反正我是信了。说了很多，
'最后以一个小例子结尾吧:

Sub Test()
Dim reg As New RegExp
With reg
.Global = True
.IgnoreCase = True
.Pattern = "\d+"
End With

Dim mc As MatchCollection
Dim m As match
Set mc = reg.Execute("123aaaaa987uiiui999")
For Each m In mc
MsgBox m.Value
Next
End Sub
Function ExReplace(sStr As String, sReplStr As String, sPatrn As String) As String
'正则表达式替换
'参数说明：sStr原字符串，sReplStr将替换的字符串，sPatrn样式
'返回参数，返回替换后的值
Dim regEX As Object
Set regEX = CreateObject("VBSCRIPT.REGEXP") 'RegEx为建立正则表达式
regEX.Global = True '设置全局可用
regEX.Pattern = sPatrn '设置样式
ExReplace = regEX.Replace(sStr, sReplStr) '执行替换
Set regEX = Nothing
End Function
Function ExExce(sStr As String, sPatrn As String, Optional IC As Boolean = True, Optional G As Boolean = True) As Object
'正则表达式搜索
'参数说明：sStr原字符串，Patrn样式,IC是否区别大小写,G是否全局可用
'返回参数，返回的是一个对象，ExExce.Count是搜索的数量
' ExExce(n).FirstIndex搜索的第n个串的位置，n>=0
' ExExce(n).Value搜索的第n个串的值，n>=0
Dim regEX As Object
Set regEX = CreateObject("VBSCRIPT.REGEXP") 'RegEx为建立正则表达式
regEX.Global = True '设置全局可用
regEX.Pattern = sPatrn '设置样式
regEX.IgnoreCase = IC '设置是否区分大小写。
Set ExExce = regEX.Execute(sStr) '执行搜索
Set regEX = Nothing
End Function
Function ExTest(sStr As String, sPatrn As String, IC As Boolean) As Boolean
'正则表达式匹配
'VBA代码
'参数说明：sStr原字符串，Patrn样式,IC是否区别大小写
'返回参数，返回一个逻辑值，True为匹配，False为不匹配
Dim regEX As Object
Set regEX = CreateObject("VBSCRIPT.REGEXP") 'RegEx为建立正则表达式
regEX.Global = True '设置全局可用
regEX.Pattern = sPatrn '设置样式
regEX.IgnoreCase = IC '设置是否区分大小写。
ExTest = regEX.Test(sStr) '执行搜索测试
Set regEX = Nothing
End Function
Public Sub 去重复()
Dim ss, re, rv
ss = "Is is the cost of of gasoline going up up?." & vbNewLine
Set re = New RegExp
re.Pattern = "\b([a-z]+) \1\b"
re.Global = True
re.IgnoreCase = True
re.MultiLine = True
rv = re.Replace(ss, "$1")
MsgBox rv
End Sub
Function RegExpTest(patrn, strng)
'正则表达式(RegExp)对象
'提供简单的正则表达式支持功能?
'下面的代码说明了RegExp对象的用法:
Dim regEX, match, matches ' 建立变量。
Set regEX = New RegExp ' 建立正则表达式。
regEX.Pattern = patrn ' 设置模式。
regEX.IgnoreCase = True ' 设置是否区分字符大小写。
regEX.Global = True ' 设置全局可用性。
Set matches = regEX.Execute(strng) ' 执行搜索。
For Each match In matches ' 遍历匹配集合。
retStr = retStr & "Match found at position "
retStr = retStr & match.FirstIndex & ". Match Value is '"
retStr = retStr & match.Value & "'." & vbCrLf
Next
RegExpTest = retStr
End Function
'Global 属性
'设置或返回一个 Boolean 值，该值指明在整个搜索字符串时模式是全部匹配还是只匹配第一个。
'Object.Global [= True | False ]
'对象 参数总是 RegExp 对象。如果搜索应用于整个字符串，Global 属性的值为 True，否则其值为 False。默认的设置为 False。
'下面的代码说明了 Global 属性的用法（改变赋予 Global 属性的值并观察其效果）：
Function RegExpTest1(patrn, strng)
Dim regEX, match, matches ' 建立变量。
Set regEX = New RegExp ' 建立规范表达式。
regEX.Pattern = patrn ' 设置模式。
regEX.IgnoreCase = True ' 设置是否区分字母的大小写。
regEX.Global = True ' 设置全程性质。
Set matches = regEX.Execute(strng) ' 执行搜索。
For Each match In matches ' 重复匹配集合
retStr = retStr & "Match found at position "
RetStr=RetStr&Match.FirstIndex&".Match Value is '"
RetStr=RetStr&Match.Value&"'."&vbCRLF Next
RegExpTest = retStr
End Function
'IgnoreCase 属性
'设置或返回一个Boolean值，指明模式搜索是否区分大小写。
'Object.IgnoreCase [= True | False ]
'Object 参数总是一个 RegExp 对象。如果搜索是区分大小写的，则 IgnoreCase 属性为 False；否则为 True。缺省值为 False。
'说明
'下面的代码说明了 IgnoreCase 属性的用法（改变赋予 IgnoreCase 属性的值以观察其效果)：
Function RegExpTest2(patrn, strng)
Dim regEX, match, matches ' 建立变量。
Set regEX = New RegExp ' 建立正则表达式。
regEX.Pattern = patrn ' 设置模式。
regEX.IgnoreCase = True ' 设置不区分大小写。
regEX.Global = True ' 设置全局可用性
Set matches = regEX.Execute(strng) ' 执行搜索。
For Each match In matches ' 重复匹配集合
retStr = retStr & "Match found at position "
RetStr=RetStr&Match.FirstIndex&".Match Value is '"
RetStr=RetStr&Match.Value&"'."&vbCRLF
Next
RegExpTest = retStr
End Function
'Execute 方法
'对指定的字符串执行正则表达式搜索?
'object.Execute(string)
'参数
'Object
'必选项。总是一个 RegExp 对象的名称。
'string
'必选项?要在其上执行正则表达式的文本字符串?
'说明
'正则表达式搜索的设计模式是通过 RegExp 对象的 Pattern 来设置的。
'Execute 方法返回一个 Matches 集合，其中包含了在 string 中找到的每一个匹配的 Match 对象。如果未找到匹配，Execute 将返回空的 Matches 集合。
'下面的代码说明了 Execute 方法的用法。
Function RegExpTest3(patrn, strng)
Dim regEX, match, matches ' Create variable.
Set regEX = New RegExp ' Create a regular expression.
regEX.Pattern = patrn ' Set pattern.
regEX.IgnoreCase = True ' Set case insensitivity.
regEX.Global = True ' Set global applicability.
Set matches = regEX.Execute(strng) ' Execute search.
For Each match In matches ' Iterate Matches collection.
retStr = retStr & "Match found at position "
retStr = retStr & match.FirstIndex & ". Match Value is '"
retStr = retStr & match.Value & "'." & vbCrLf
Next
RegExpTest = retStr
End Function
'Replace 方法
'替换在正则表达式查找中找到的文本?
'object.Replace(string1, string2)
'参数
'Object
'必选项。总是一个 RegExp 对象的名称。
'string1
'必选项?string1 是将要进行文本替换的字符串?
'string2
'必选项。 string2 是替换文本字符串。
'说明
'被替换的文本的实际模式是通过 RegExp 对象的 Pattern 属性设置的。
'Replace 方法返回 string1 的副本，其中的 RegExp.Pattern 文本已经被替换为 string2。如果没有找到匹配的文本，将返回原来的 string1 的副本。
'下面的例子说明了 Replace 方法的用法。
Function ReplaceTest4(patrn, replStr)
Dim regEX, str1 ' 建立变量。
str1 = "The quick brown fox jumped over the lazy dog."
Set regEX = New RegExp ' 建立正则表达式。
regEX.Pattern = patrn ' 设置模式。
regEX.IgnoreCase = True ' 设置是否区分大小写。
ReplaceTest = regEX.Replace(str1, replStr) ' 作替换。
End Function
'MsgBox(ReplaceTest("fox", "cat")) ' 将 'fox' 替换为 'cat'。
'；另外，Replace 方法在模式中替换 subexpressions 。 下面对以前示例中函数的调用，替换了原字符串中的所有字对：
'MsgBox (ReplaceText("(\S+)(\s+)(\S+)", "$3$2$1")) ' 交换词对.

'test 方法
'对指定的字符串执行一个正则表达式搜索，并返回一个 Boolean 值指示是否找到匹配的模式。
'object.Test(string)
'参数
'Object
'必选项。总是一个 RegExp 对象的名称。
'string
'必选项?要执行正则表达式搜索的文本字符串?
'说明
'正则表达式搜索的实际模式是通过RegExp对象的Pattern属性来设置的?RegExp.Global属性对Test方法没有影响?
'如果找到了匹配的模式，Test方法返回True；否则返回False。
'下面的代码说明了Test 方法的用法?
Function RegExpTest5(patrn, strng)
Dim regEX, retVal ' 建立变量。
Set regEX = New RegExp ' 建立正则表达式。
regEX.Pattern = patrn ' 设置模式。
regEX.IgnoreCase = False ' 设置是否区分大小写。
retVal = regEX.Test(strng) ' 执行搜索测试。
If retVal Then
RegExpTest = "找到一个或多个匹配。"
Else
RegExpTest = "未找到匹配。"
End If
End Function

'MsgBox (RegExpTest("is.", "IS1 is2 IS3 is4"))

'Match 对象
'提供了对正则表达式匹配的只读属性的访问?
'说明
'Match 对象只能通过 RegExp 对象的 Execute 方法来创建，该方法实际上返回了 Match 对象的集合。所有的 Match 对象属性都是只读的。
'在执行正则表达式时，可能产生零个或多个 Match 对象。每个 Match 对象提供了被正则表达式搜索找到的字符串的访问、字符串的长度，以及找到匹配的索引位置等。
'下面的代码说明了 Match 对象的用法：

Function RegExpTest6(patrn, strng)
Dim regEX, match, matches ' 建立变量。
Set regEX = New RegExp ' 建立正则表达式。
regEX.Pattern = patrn ' 设置模式。
regEX.IgnoreCase = True ' 设置是否区分大小写。
regEX.Global = True ' 设置全局替换。
Set matches = regEX.Execute(strng) ' 执行搜索。
For Each match In matches ' 遍历 Matches 集合。
retStr = retStr & "Match " & i & " found at position "
retStr = retStr & match.FirstIndex & ". Match Value