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 As
VbCompareMethodLong = 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)
Replace97 = sTemp
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.