Errors using VBScript_RegExp_55.RegExp's Replace method

Question

I am following a few online examples of how to use regular expressions to replace text in VBA:

http://msdn.microsoft.com/en-us/library/xwewhkd1(v=vs.110).aspx

http://stackoverflow.com/questions/5539141/microsoft-office-access-like-vs-regex

http://technet.microsoft.com/en-us/library/2008.05.heyscriptingguy.aspx?f=255&MSPPError=-2147217396

http://www.macrostash.com/2011/10/08/simple-regular-expression-tutorial-for-excel-vba/#codesyntax\_5

Here is my first crack at it:

   Function xpandProvince( inStr As String ) As String

      Dim regEx As New VBScript_RegExp_55.RegExp

      Dim Matches 'I don't really use this (yet)

      regEx.IgnoreCase = False

      regEx.Global = True

      regEx.MultiLine = True

      regEx.Pattern = "\bAB\b"

      xpandProvince = regEx.Replace( inStr, "Alberta" )

      regEx.Pattern = "\bBC\b"

      xpandProvince = regEx.Replace( inStr, "British Columbia" )

      regEx.Pattern = "\bMB\b"

      xpandProvince = regEx.Replace( inStr, "Manitoba" )

      regEx.Pattern = "\bNB\b"

      xpandProvince = regEx.Replace( inStr, "New Brunswick" )

      regEx.Pattern = "\bNL\b"

      xpandProvince = regEx.Replace( inStr, "Newfoundland" )

      regEx.Pattern = "\bNS\b"

      xpandProvince = regEx.Replace( inStr, "Nova Scotia" )

      regEx.Pattern = "\bNT\b"

      xpandProvince = regEx.Replace( inStr, "Northwest Territories" )

      regEx.Pattern = "\bNU\b"

      xpandProvince = regEx.Replace( inStr, "Nunavut" )

      regEx.Pattern = "\bON\b"

      xpandProvince = regEx.Replace( inStr, "Ontario" )

      regEx.Pattern = "\bPE\b"

      xpandProvince = regEx.Replace( inStr, "Prince Edward Island" )

      regEx.Pattern = "\bPEI\b"

      xpandProvince = regEx.Replace( inStr, "Prince Edward Island" )

      regEx.Pattern = "\bQC\b"

      xpandProvince = regEx.Replace( inStr, "Quebec" )

      regEx.Pattern = "\bSK\b"

      xpandProvince = regEx.Replace( inStr, "Saskatchewan" )

      regEx.Pattern = "\bYT\b"

      xpandProvince = regEx.Replace( inStr, "Yukon" )

   End Function 'xpandProvince

I think I've followed all the details in the webpage examples above, but Access's VBA IDE  reports "Compile error: Syntax error" for all of the regEx.Replace invocations.  I'd appreciate any advice on how to get this working.

Answer 1

InStr is a built-in function, so it is not suitable as the name for the argument of the function.

You overwrite xpandProvince each time, instead of accumulating the changes.

Function xpandProvince(InputString As String) As String

    Dim regEx As Object

    Dim Matches 'I don't really use this (yet)

    Dim ReturnString As String

    Set regEx = CreateObject("VBScript.RegExp")

    regEx.IgnoreCase = False

    regEx.Global = True

    regEx.MultiLine = True

    ReturnString = InputString

    regEx.Pattern = "\bAB\b"

    ReturnString = regEx.Replace(ReturnString, "Alberta")

    regEx.Pattern = "\bBC\b"

    ReturnString = regEx.Replace(ReturnString, "British Columbia")

    regEx.Pattern = "\bMB\b"

    ReturnString = regEx.Replace(ReturnString, "Manitoba")

    regEx.Pattern = "\bNB\b"

    ReturnString = regEx.Replace(ReturnString, "New Brunswick")

    regEx.Pattern = "\bNL\b"

    ReturnString = regEx.Replace(ReturnString, "Newfoundland")

    regEx.Pattern = "\bNS\b"

    ReturnString = regEx.Replace(ReturnString, "Nova Scotia")

    regEx.Pattern = "\bNT\b"

    ReturnString = regEx.Replace(ReturnString, "Northwest Territories")

    regEx.Pattern = "\bNU\b"

    ReturnString = regEx.Replace(ReturnString, "Nunavut")

    regEx.Pattern = "\bON\b"

    ReturnString = regEx.Replace(ReturnString, "Ontario")

    regEx.Pattern = "\bPE\b"

    ReturnString = regEx.Replace(ReturnString, "Prince Edward Island")

    regEx.Pattern = "\bPEI\b"

    ReturnString = regEx.Replace(ReturnString, "Prince Edward Island")

    regEx.Pattern = "\bQC\b"

    ReturnString = regEx.Replace(ReturnString, "Quebec")

    regEx.Pattern = "\bSK\b"

    ReturnString = regEx.Replace(ReturnString, "Saskatchewan")

    regEx.Pattern = "\bYT\b"

    ReturnString = regEx.Replace(ReturnString, "Yukon")

    xpandProvince = ReturnString

End Function 'xpandProvince

Answer 2

I would have used REPLACE if I could find a way to anchor the match to beginning and end of a word, even if there is no adjacent delimiter character (which I take to be any character that wouldn't be used in a variable name).  For example, if the 2-3 letter acronym was at the start or end of a string.  Is there a way to do this using REPLACE?

Answer 3

In such a car there is little to be gained in using RegEx.  Why not simply use the Replace function?

Answer 4

Doh!  Thanks!