Bolte IT Consulting

Regular Expressions (RegExp) in VBA

The following code snippet will show you how to use regular expressions in VBA.

Source Code

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
27
28
' @Author - Alexander Bolte
' @Change Date - 2014-02-28
' @Description - Searches given a string for a given regular expression and returns the result based on parametrization in a collection.
' @Param findAll - True, if all occurences matching the given pattern should be returned. False, if only the first occurence matching a given pattern should be returned.
' @Returns - A collection of type VBA.Collection holding all found matches as Strings.
Function getRegExMatches(ByVal patrn As String, ByVal strng As String, Optional ByVal findAll As Boolean = True, Optional ByVal useIgnoreCase As Boolean = False) As VBA.Collection
    Dim regEx As RegExp
    Dim matches As MatchCollection
    Dim ret As New VBA.Collection
    Dim i As Integer
    
    Set regEx = New VBScript_RegExp_55.RegExp
    regEx.Pattern = patrn   ' Set pattern.
    regEx.IgnoreCase = useIgnoreCase   ' Set case insensitivity / sensitivity.
    regEx.Global = findAll   ' Set global applicability. If true, find all occurences, if false, find only fisrt one
    regEx.MultiLine = True ' This parameter controls, if a pattern should be searched accross line breaks or not.
    If regEx.Test(strng) Then
        Set matches = regEx.Execute(strng)   ' Execute search.
        For i = 0 To matches.Count - 1
            ret.Add matches.Item(0).Value
        Next i
    End If
    
    Set matches = Nothing
    Set regEx = Nothing
    
    Set getRegExMatches = ret
End Function

Referenced APIs

This code references the folowing API, which is a VBScript API.

"Microsoft VBScript Regular Expression Reference 5.5"

 

In order to reference this API in Excel VBA goto Options -> References (In German "Extras" -> "Verweise") and tick the API above.

Additional Resources

Here is a link to a more detailed article on regular expressions in VBScript and VBA.

http://msdn.microsoft.com/en-us/library/ms974570.aspx

And here a link on regular expressions in general. I found it very helpful, as I would not consider myself someone who fully understands regular expressions.

I just like to use them as they make my life much easier.

http://www.regular-expressions.info/

Since I do not wanrt to spend a lot of time on testing my regular expressions in my tools, here a link on a regex tester.

http://regexpal.com/ 

Hits: 15580