Using Regular Expressions in IDEAScript and Custom Functions

Regular Expressions or regexp is a powerful language that allows you to search for word patterns or replace word patterns with another pattern.  It is so powerful that it can be considered a language onto itself.

Microsoft defines it as "Regular expressions provide tools for developing complex pattern matching and textual search-and-replace algorithms."

This VBA functionality is available in IDEAScript and Custom Functions.  The following is some example code on how to access it that I obtained from the CaseWare IDEA web site.  I am hoping to have a series of posts explaining the different functionality and how to use it as it is a very powerful feature that can be quite useful.

Dim RE As Object
Dim REMatches As Object
Dim re6 As String
Set RE = CreateObject("vbscript.regexp")
RE.MultiLine = False
RE.Global = False
RE.IgnoreCase = True
RE.Pattern = reg_exp
    
Set REMatches = RE.Execute(input_string)
    
If rematches.count = 0 Then Exit Function
    
RE6 = REMatches(0)

In the above code you first create an object to hold the Regular Expressions object and the match results from the Regular Expressions object. 

As regexp is not a standard in the IDEAScripting language you have to use the CreateObject to obtain the regexp functionality.

MultiLine allows for searchs over more than one line,

Global allows you to do replacement over the entire database instead of just the first instance.

IgnoreCase, I think this is fairly obvious, it will ignore any case differences.

Pattern is the expression that you are looking for or replacing.  These expression can be quite complex but also you can use it to validate information such as email addresses, urls or pretty much anything.

Finally you perform the search on the string or field and it will return the number of matches.