在VB6/VBA中使用正则表达式

一、关于起因

最近在Office的QQ群里问如何在一串字符串中提取数值并加总的问题。如果使用正则表达式可以非常迅速的解决这个问题。

那么今天我就探讨一下在VB6/VBA中使用正则表达式的方法及代码，另外为了快速测试正则表达式，我给大家推荐notepad++及使用方式。

二、操作步骤

1、按Alt+F11进入Excel的VBE环境，依次选择“工具/引用”菜单，选择“Microsoft VBScript Regular Express”；

2、插入一个模块，在模块中输入如下所示的代码；

Function SumValueInText(TargetRange As Range) As Double
    Dim mRegExp As RegExp
    Dim mMatches As MatchCollection      '匹配字符串集合对象
    Dim mMatch As Match        '匹配字符串

    Set mRegExp = New RegExp
    With mRegExp
        .Global = True                              'True表示匹配所有, False表示仅匹配第一个符合项
        .IgnoreCase = True                          'True表示不区分大小写, False表示区分大小写
        .Pattern = "([0-9])?[.]([0-9])+|([0-9])+"   '匹配字符模式
        Set mMatches = .Execute(TargetRange.Text)   '执行正则查找，返回所有匹配结果的集合，若未找到，则为空
        For Each mMatch In mMatches
            SumValueInText = SumValueInText + CDbl(mMatch.Value)
        Next
    End With
    
    Set mRegExp = Nothing
    Set mMatches = Nothing
End Function

3、在工作表的A列单元格中输入各种测试字符串，在B列单元格中输入自定义函数进行测试，结果如下图所示；

三、Attention

在VB6/VBA中使用正则表达式时，我们也可以省去第一步，即采用后期绑定的方式来使用正则表达式，但是代码要做相应的调整，如下所示为调整后的代码。

Function SumValueInText(TargetRange As Range) As Double
    Dim mRegExp As Object       '正则表达式对象
    Dim mMatches As Object      '匹配字符串集合对象
    Dim mMatch As Object        '匹配字符串

    Set mRegExp = CreateObject("Vbscript.Regexp")
    With mRegExp
        .Global = True                              'True表示匹配所有, False表示仅匹配第一个符合项
        .IgnoreCase = True                          'True表示不区分大小写, False表示区分大小写
        .Pattern = "([0-9])?[.]([0-9])+|([0-9])+"   '匹配字符模式
        Set mMatches = .Execute(TargetRange.Text)   '执行正则查找，返回所有匹配结果的集合，若未找到，则为空
        For Each mMatch In mMatches
            SumValueInText = SumValueInText + CDbl(mMatch.Value)
        Next
    End With
    
    Set mRegExp = Nothing
    Set mMatches = Nothing
End Function