Simulates the Replace function that is not available in Excel 97.
Function Replace97(ByVal sExpression As String, _
sFind As String, sReplace As String, _
Optional lStart As Long = 1, _
Optional lCount As Long = -1, _
Optional cCompare AsVbCompareMethodLong = vbBinaryCompare) As String
Dim sTemp As String
Dim lIntCnt As Long
Dim i As Long
Dim lFound As Long
sTemp = Mid(sExpression, lStart, Len(sExpression) – lStart + 1)
lIntCnt = lCount
lFound = InStr(1, sTemp, sFind, cCompare)
Do While CBool(lIntCnt) And (lFound > 0)
sTemp = Left(sTemp, lFound – 1) & _
sReplace & _
Right(sTemp, Len(sTemp) – lFound + 1 – Len(sFind))
lIntCnt = lIntCnt – 1
lFound = InStr(lFound + 1, sTemp, sFind, cCompare)
lFound = InStr(lFound + Len(sReplace), sTemp, sFind, cCompare)
Loop
Replace97 = sTemp
End Function
BUG: I was trying to a replace and ampersand (&) with two ampersands (&&) and noted a bug in this code. The struck through code above looks for the next instance of sFind starting at the previous instance plus one character. When you replace a single character with two of the same character, it finds the new second character and goes into an infinite loop. So lFound + 1 was replaced with lFound + Len(sReplace) so that it starts looking for the next instance after the replaced text.
BUG #2:Neil pointed out that using vbCompareMethod as a data type doesn’t work in Excel97. It has been changed to Long in the above code, which is what I should have used regardless of the version.
Hey, how about using the SUBSTITUTE function instead ?
Application.Substitute(sText, sFind, sReplace, [Instance])
Juan: Substitute works for most cases, but it lacks some flexibility like how many instances to replace, where to start, and compare method. Also, it doesn’t mirror the Replace function which only returns text from the Start argument, not the whole string.
Yes, good points Dick.
The function fails on an Office 97 box on compilation.
The error returned suggests the type ‘VbCompareMethod’ or value vbBinaryCompareis not supported.
Any idea why?
I am calling the function from a macro in Excel 97
You are my savior. The replacing of the single ampersand to double was KILLING me. Thanks for the function.
When we upgrade above Excel 97, will this still be needed, or is a Replace function accepted in these later versions?
You just never know the effect you have…
I used the curent Replace function in a cross-platform Excel solution (version 2003 -2007) and it threw an error in the Mac version. After some reading and determinig that the VBA engine in Excel:Mac uses the 97 VBA engine I was starting to think I would have to re-create the function. Fortunately finding your solution saved me that time. So even though you wrote the function over four years ago you can take comfort in the fact that your code is still making an impact today…THANKS!!!
The vbCompareMethod had me really stumped under Excel 97 until I found this page. It is surprising how many places on the Internet have the code with vbCompareMethod still in it. Changing to Long solved all the problems. MANY THANKS … STILL!!