View Single Post
Old 07-30-2012, 02:27 PM   #13
Newly Registered User
Join Date: Mar 2002
Location: New York
Posts: 741
Thanks: 12
Thanked 1 Time in 1 Post
lala is on a distinguished road
Send a message via AIM to lala
Re: match whole word in vba

in case anyone needs it, here's amazing code written by HansUp at StackOverflow

Public Function RegExpReplaceWord(ByVal strSource As String, ByVal strFind As String, ByVal strReplace As String) As String
' Purpose   : replace [strFind] with [strReplace] in [strSource] '
' Comment   : [strFind] can be plain text or a regexp pattern; '
'             all occurences of [strFind] are replaced '
    'requires reference to Microsoft VBScript Regular Expressions '
    'Dim re As RegExp '
    'Set re = New RegExp '
    'late binding; no reference needed '
    Dim re As Object
    Set re = CreateObject("VBScript.RegExp")

    re.Global = True
    re.IgnoreCase = True ' <-- case insensitve
    re.Pattern = "\b" & strFind & "\b"
    RegExpReplaceWord = re.Replace(strSource, strReplace)
    Set re = Nothing

End Function
lala is offline   Reply With Quote