Public Sub ReplaceAll(ByRef sOrigStr As String, _
ByVal sFindStr As String, _ ByVal sReplaceWithStr As String, _ Optional bWholeWordsOnly As Boolean) ' ' Replaces all occurances of sFindStr with sReplaceWithStr ' Dim lPos As Long Dim lPos2 As Long Dim sTmpStr As String Dim bReplaceIt As Boolean Dim lFindStr As Long On Error GoTo vbErrorHandler lFindStr = Len(sFindStr) lPos2 = 1 bReplaceIt = True sTmpStr = sOrigStr Do lPos = InStr(lPos2, sOrigStr, sFindStr) If lPos = 0 Then Exit Do End If If bWholeWordsOnly Then On Error Resume Next If lPos = 1 Or (Mid$(sOrigStr, lPos - 1, 1) = " ") Then If (Mid$(sOrigStr, lPos + lFindStr, 1) = " ") Or Mid$(sOrigStr, lPos + lFindStr + 1, 1) = "" Then bReplaceIt = True Else bReplaceIt = False End If End If End If If bReplaceIt Then If lPos > 1 Then sTmpStr = Left$(sOrigStr, lPos - 1) Else sTmpStr = "" End If sTmpStr = sTmpStr & sReplaceWithStr sTmpStr = sTmpStr & Mid$(sOrigStr, lPos + lFindStr, Len(sOrigStr) - (lPos + lFindStr - 1)) sOrigStr = sTmpStr End If lPos2 = lPos + 1 Loop sOrigStr = sTmpStr Exit Sub vbErrorHandler: Err.Raise Err.Number, "StrHandler ReplaceAll", Err.Description End Sub |