在Excel VBA中使用正则表达式
在Excel中使用正则式，需要借助于Excel自带的Visual Basic环境。我查了一下Microsoft Office Excel 2007 Formulas & Functions FOR DUMmIES以及Excel VBA Programming For Dummies，两本书中均没有关于正则表达式的函数，也没有关于如何在VB中使用正则表达式的介绍。不过，我还是在google中使用excel 和regex 作为关键词找到了答案，总结在这里。我没怎么使用过VB，这里的代码只是浅尝辄止式，能够运行而已。本文假设读者有实际操作Excel VBA的经验。
1. 创建按钮
在Excel中可以方便地加入按钮，方法是右键点击菜单栏空白处，选中Visual Basic选项，在弹出的VB工具栏中点击控件工具箱，然后点击其中的“命令按钮”，再在当前工作表中合适位置拖动，即可创建一个按钮。如图所示。
2. 代码框架：将该按钮与VB代码相关联。
在菜单->宏->Visual Basic 编辑器中，为CommandButton1_Click()添加代码如下：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | Private Sub CommandButton1_Click() Dim RegEx As Object Dim Myrange As Range, C As Range ' 将RegEx设置为vbscript.regexp正则对象 Set RegEx = CreateObject("vbscript.regexp") ' 设置RegExp 的属性 With RegEx 'look for global matches 全局匹配 .Global = True ' 选择非数字的文本 .Pattern = "[^\d]+" End With ' 将当前活动工作表中的所有批注赋值给cmt Set cmt = ActiveSheet.Comments For Each xC In cmt '对于每一条批注,删除其中的非数字文本,只留下数字文本 xC.Text RegEx.Replace(xC.Text, "") Next Set Myrange = Nothing Set RegEx = Nothing End Sub |
上面代码的作用是将当前活动工作表中所有的批注中的非数字内容删除。例如，如果某单元格的批注为“ab1234cd” ，则执行代码后，其结果是“1234”。
回到工作表中，新建几条同时含数字和非数字内容的批注，执行代码，则其中的非数字项会被删除。
3. 分析代码
步骤2展示的是如何在Excel中调用正则表达式来处理文本。总结一下，其过程如下：
执行正则表达式时，我们有3种函数可用：Test、Execute、Replace。
- Test：检测目标文本与正则式是否匹配，返回True or False。
- Execute：检测是否匹配，并返回匹配集合，供后续程序调用处理。
- Replace：执行替换操作。在替换时，可以使用$1,$2等捕获变量，与常规的正则表达式意义相同，不赘述。
4. 应用举例
单纯的正则式应用很简单，难点在于在不同的场合和环境中，结合目标语言，正确使用它。在Excel中使用正则式，我认为应该特别注意的地方是Excel的独特的变量、活动表格、单元格范围，诸如此类。不过，这些内容应该可以通过查询帮助文档来获得。文末附了几本与Excel、VB编程相关的书籍，可供参考。
这里再举一则Execute函数的例子。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | Private Sub CommandButton2_Click() Dim myRegExp As Object Dim Myrange As Range, C As Range Set myRegExp = CreateObject("vbscript.regexp") myRegExp.Pattern = "(\d{4})(\w{2})" Set Myrange = ActiveSheet.Range("B3:B7") For Each C In Myrange Set myMatches = myRegExp.Execute(C.Value) If myMatches.Count >= 1 Then Set myMatch = myMatches(0) If myMatch.SubMatches.Count >= 2 Then MsgBox myMatch.SubMatches(0) '第一组匹配，(\d{4})部分 MsgBox myMatch.SubMatches(1) '第二组匹配，(\w{2})部分 End If End If Next End Sub |
本程序的作用是将当前活动工作表中B3:B12范围单元格内的连续4位的数字取出来，使用MsgBox形式输出。执行结果为：
- B3单元格匹配，结果为2323、ac。
- B7单元格匹配，结果为3233、23。
文中的程序在MS Office 2003 Excel下通过。
附：与Excel、VBA、正则表达式相关的一些资源：
- RegExp Sept 1 2004.xls荐！，一个200k的excel文档（英文），Excel下正则式运用之典范。运行时请将工具->宏->安全性调至低。
- Excel 2007 Formulas & Functions FOR DUMmIES （386页英文PDF，9.16Mb，需登录该页面下载，下同。）
- Excel VBA Programming For Dummies （425页英文PDF，6.18Mb）
原来如此，我还以为如何^_^
[Reply]
請問使用Excel VBA要如何找出雙引號或單引號中的內容呢?
例如:下面4行
define(“_MB_NEWBB_VIEWS”,”人氣”);
define(‘_MB_NEWBB_CRITERIA_TOPIC’,'主題’);
define(‘_MB_NEWBB_TIME_DESC’,”正數為天數, 負數為小時”);
define(“_MB_NEWBB_CRITERIA_TEXT”,’文章內容’);
我在Expresso中的Test Mode下["'].+?["'],可得到想要的結果.
但在Excel VBA中要如何使用呢？
[Reply]
.rex Reply:
May 2nd, 2009 at 6:37 am
＠s953904:
欢迎来自宝岛的朋友。你所问的问题的解答，已经以excel附件形式，发到你的邮箱了。code在excel内。
匹配雙引號或單引號的正则表达式其基本语法如你所写：["']. ?["']。
不过，为了精确，一般我们写作：(["']). ?1，这样是确保前后的引号一致，即，如果前面是单引號，则后面也是；是双引号时亦然。
在Excel中，单行模式（默认）下，我们可以使用.来匹配任意字符，此时.不能匹配换行符。
多行模式是明确指出的，格式如：myRegExp.MultiLine = True
此时，使用[sS]来代替点号.。
在Excel中，全局模式需要明确指出：myRegExp.Global = True
[Reply]
感謝,說明及範例郵件已收到.
[Reply]
你好，我尝试用\u4e2a和\u6708识别中文“个”和“月”，结果提示错误。有什么技巧吗？
[Reply]
不好意思，忘了说明环境：VBA，Excel 2003
[Reply]
rex Reply:
January 28th, 2010 at 8:14 pm
直接用汉字“个”和“月”来匹配试试？好像支持中文的。
[Reply]
RegExp Sept 1 2004.xls这个文件无法下载了，搜索中发现一个网站http://regexlib.com，好象是一个正则表达式的案例库，感觉不错，只是英文的。
[Reply]
Function getphone(regex, n)
Dim regex, Match, Matches
Set regex = New RegExp
With regex
.Pattern = “^(\(\d{3,4}\)|\d{3,4}-|\s)?\d{8}”
.IgnoreCase = True
.Global = True
Set Matches = .Execute(n)
End With
For Each Match In Matches
retstr = Match.Value
Next
getphone = retstr
End Function
VBA请帮忙看下有什么不对？thanks.
[Reply]