View Single Post
Old 07-30-2012, 03:27 PM   #13
lala
Newly Registered User
 
Join Date: Mar 2002
Location: New York
Posts: 740
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
http://stackoverflow.com/questions/1...65679_11729848



Code:
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