Excel VBA 基础(04.2) - 常用扩展库之RegExp （正则表达式）
上一节的示例文件中已经包含了源代码。程序本身原理比较简单，这里不再重复。如有疑问请留言。
关于多重条件的汇总请参考 实战部分。
进入本章主题RegExp 即 正则表达式，解决字符串相关问题的终极武器。
如果有其他编程语言基础的朋友们仅仅需要注意VB相关的语法即可。
引入问题：试设想如下场景，从pdf文件中将科目余额汇总表 拷贝出来的时候，你发现原本表格式存储的数据 粘贴到Excel 当中 都挤在了同一行，已知，
- 会计科目代码组成遵循以下模式，
四位一级科目代码.两位二级科目代码.两位三级科目代码
例如 1141 1141.10 可能分别代表 应收账款 以及 应收账款-华中地区
- 科目名称 可能由字符数字以及空格组成
- 金额，包含千分位符号，用零补齐的两位小数，负数加括号 例如 1,234.05 以及 (1.00)
现在要求把 会计科目代码，科目名称以及金额原样分成三列，金额项保存相应的值并通过单元格格式来达到指定效果
如
1141.10.00应收账款 华中地区(0)(100.00)
转化为 如下 三列
1141.10.00 | 应收账款 华中地区(0) | -100
了解问题之后，我们考虑，
1# 是否可以采用Excel内置的分列功能，字段不定长并且无统一的分隔符号，故不可行，
2# 是否可以让Excel自动识别，Ctrl+E，亲测无效，
3# 如果工作量在50行以内，在不保障准确率的情况下，手工处理尚可以接受，
除了让客户再发一个Excel版本之外，下面我们就来介绍，通过VBA实现的更加优雅的解决方式。
引入正则表达式(Regular Expression, 以下简称RegExp)变量声明，固定写法
Dim reg As Object
Set reg = CreateObject("vbscript.regexp")
进入正则表达式 核心部分 匹配模式。正则为我们提供了丰富的基础模式，
现在重点讲解实践中常用的一些模式
. 匹配任意字符
/ 之后的任意特殊字符 匹配其本身 如 /. 匹配 .
0-9 以及 a-zA-Z 以及汉字字符 匹配其本身 a{5} 即a出现5次 匹配 aaaaa 大括号用法见下文
/d 匹配数字。等价于[0-9]， 25/d 匹配 250 到 259 之间的字符串
/D 匹配非数字。等价于[^0-9]
/s 匹配空白，包括空格、制表符、换页符等。等价于"[/f /n /r /t /v ]"
/S 匹配非空白的字符。等价于"[^/f /n /r /t /v ]"
/w 匹配字母、数字，以及下划线。等价于"[A-Za-z0-9_]"
/W 匹配非字符数字。等价于"[^A-Za-z0-9/_]"
我们注意到，大写字母为小写字母所表示模式的补集
定义出现频率
{a,b} 其中 a b分别为相应模式出现次数的上限与下限 /d{1,4} 表示一到四位数字
{a} 其中 a 为相应模式出现次数 /d{4} 表示由任意数字组成的四位字符串
? 出现一次或不出现 Germany? 既可以匹配 German 又可以匹配 Germany
+ 出现一次以上 a+ 既可以匹配 aaa
* 可能不出现 出现一次 或出现多次
[] 中括号中的模式选一 如[abcd0123] 表示从abcd0123这几个字符中任意选择一个
也可以表示为 [a-d0-3]
[^] 表示 除括号中元素之外的其他所有
| 表示二选一 ma|en 可以匹配 man 也可以匹配men
() 表示 分组 Eng(lish)|(land) 可以匹配 English 也可以匹配England
^ 为字符串开头
$ 为字符串结尾
正则表达式的水很深，这里仅仅针对示例进行基础性的讲解，实践中针对复杂的正则匹配模式，查询API必不可少。
下面我们来分析示例中的匹配模式。正则的特点是书写方便但是极其不便于阅读，重点明白原理后结合API实现即可
首先是 会计科目代码
^\s*(\d{4}(?:\.\d{2}(?:\.\d{2})?)?)
^ 为字符串开头，防止空格或其他空白出现加上 \s*， 后面非空字符加括号单独分组加括
\d{4} 4位一级科目代码 必然会出现
(?:\.\d{2}(?:\.\d{2})?)? 为2位 第二， 第三级科目，出现第二级科目后才会有第三级存在， 注意分组之后的 ? 可能出现也可能不出现
\. 表示科目代码分隔的点号，注意 用\ 转意
(?:) 表示不记录该分组 ，之后说Submatches时会用到。
会计科目名称
(.+?)
可以是任意字符串 故采用. 加号表示科目名称长度不能为0 加号之后的问号 表示如果与之后的模式冲突时，尽可能少地匹配相应的字符串 （非贪婪匹配）
如 1200存货1,000,000.00 (.+)不仅仅匹配 存货 也可以匹配后面的1,000,00 无此问号，正则会优先满足之前的模式。
金额部分
((?:[,0-9]*\d\.\d{2})|(?:\([,0-9]*\d\.\d{2}\)))\s*$
金额有两种模式，分别为 加括号与不加括号，
\d\.\d{2} 表示小数点前至少一位，小数点后至少两位
从十位起 由0到9数字以及千位分隔符组成，即 [,0-9]*
两个字分组同样不记录
VBA的写法为
With reg
.Pattern = "^\s*(\d{4}(?:\.\d{2}(?:\.\d{2})?)?)(.+?)((?:[,0-9]*\d\.\d{2})|(?:\([,0-9]*\d\.\d{2}\)))\s*$"
'.ignorecase = True
'.global = True
End With
.Pattern 为定义样式，
.ignorecase 是否忽略大小写 本例中无意义
.global 是否匹配所有，设为否匹配第一次出现的地方 本例中无意义
Dim c
If reg.test(Selection.Value) Then
For Each c In reg.Execute(Selection.Value)(0).submatches
Debug.Print c
Next c
End If
正则的方法有
.test 测试参数字符串是否匹配定义的模式
.Execute 返回匹配对象所匹配的部分，为集合，0表示匹配第一次出现的字符串，本例中第一次出现亦是整个字符串本身，
各个匹配对象都有 submatches集合，就是模式中的分组 , 之前我们说带有(?:)标记的分组不记录，也就是 这些分组不会反映在 submatches的集合里面。
由于我们将三部分分为三组，现只需要遍历取得，同时注意处理负值。整个程序如下
Option Explicit
Private Sub splitAccount()
Dim reg As Object
Set reg = CreateObject("vbscript.regexp")
With reg
.Pattern = "^\s*(\d{4}(?:\.\d{2}(?:\.\d{2})?)?)(.+?)((?:[,0-9]*\d\.\d{2})|(?:\([,0-9]*\d\.\d{2}\)))\s*$"
.ignorecase = True
End With
Dim c
Dim i
Dim tmp
' 处理欧洲 千分位分隔符号问题
With Application
.DecimalSeparator = "."
.ThousandsSeparator = ","
.UseSystemSeparators = False
End With
' 处理负值 判断是否有括号
Dim regBracket As Object
Set regBracket = CreateObject("vbscript.regexp")
With regBracket
.Pattern = "^\(([,0-9]*\d\.\d{2})\)$"
End With
' 遍历 注意转换为字符模式 加 '
For Each c In Intersect(Selection.Cells, ActiveSheet.UsedRange).Cells
If reg.test(c.Value) Then
Set tmp = reg.Execute(c.Value)(0).submatches
For i = 0 To 2
If i = 2 Then
If regBracket.test(tmp(i)) Then
c.Offset(0, 1 + i) = "-" & regBracket.Execute(tmp(i))(0).submatches(0)
c.Offset(0, 1 + i) = tmp(i)
End If
c.Offset(0, 1 + i) = "'" & tmp(i)
End If
Next i
End If
Next c
With Application
.UseSystemSeparators = True