On our testprojects, I use regular expressions most of the time for the matching or testing of strings on our Application Under Test. But sometimes I have to use the Replace method of the RegExp object. This day, I discovered an advanced usage of the RegExp.Replace() method. Let me show you with an example:

Dim replacement
Dim regEx : Set regEx = New RegExp ' Create a regular expression.
Dim myString : myString = "Cake, and grief COUNSELING, will be Available at the CoNcLuSiOn of the test."

regEx.Pattern = "\b\w+\b" ' \b = inside word boundary, \w = word character,
                          ' so this pattern matches each single word
regEx.Global = True       ' Set the scope to Global

' normally we replace like this:
replacement = "word"
MsgBox regEx.Replace(mystring, replacement)

' resulting in: "word, word word word, word word word word word word word word word."

This looks familiar I think. But it is not very sophisticated. Wouldn't it be nice if we can replace each match with a custom replacement. With the use of a Function Reference, we can!

 ' We can actually perform a custom action on replace instead of doing a fixed replace
' We do this with making a reference to a function with getref
Set replacement = getRef("Capitelize")

' We need to create a function of course with the same name and three parameters
' 1. singleMatch : the string that matched to the pattern
' 2. position : the position the singlematch string was found
' 3. fullString : the full string (same as 'myString' in this case) without _any_ replacements
Function Capitelize(singleMatch, position, fullString)

    ' Capitelize returns the string singleMatch changed with the first character
    ' in uppercase and all others in lowercase
    Capitelize = ucase(left(singleMatch,1)) & lCase(mid(singleMatch,2))
End Function

MsgBox regEx.Replace(myString, replacement)
' This results in: "Cake, And Grief Counseling, Will Be Available At The Conclusion Of The Test."

Nice huh? Now we can match for a date and replace it on the fly by the correct format. We can replace straight quote characters for curly quotes with the correct orientation with much more ease, or only do a replace if it is within a certain character range in the string. "All new possibilities arise!"

Just encountered a collegue having difficulties ensuring if a folder exists. And if a folder does not exists then it has to be created in its full path, not only the lowest level folder. The code was bloated with if/then’s, instr(), instrrev() and left()/right() functions. Even the lowest level folder name was hardcoded.

To help him out, we could go for the fancy recursive solution to show off our 1337 programming skillz, but I decided to just do it with a simple loop. Enjoy.

Public Function EnsureFolder(fPath)
    dim fso, fols, fol, pathBuild
    set fso=CreateObject("Scripting.FileSystemObject")

    fols = split(fpath, "\")

    For each fol in fols
        If fol = "" Then exit for
        pathBuild = pathBuild & fol & "\"
        If Not fso.FolderExists(pathBuild) Then
            Call fso.CreateFolder(pathBuild)
        End If
    Next

    EnsureFolder = pathBuild
End Function