If you are doing this to an array or a string in a variable, then this may
be the way.

If you are doing it to cells on the worksheet, I would use the code
equivalent to the replace command on the Edit menu and process all cells at
once (for each pair of corrections)

--
Regards,
Tom Ogilvy

"R Avery" wrote in message
oups.com...
String processing in VBA is very slow when strings are large. I have a
function that I've been using for doing batch replace ops (below), but
it chokes on large strings with many replacements to do (like 50),
because it has to do 50 passes of the string to perform the
replacements.

Has anyone written a fast function designed to do the same thing for
large strings but only makes one pass through the data?



Public Function BatchReplace(ByVal InputString As String, FindArray As
Variant, _
ReplaceArray As Variant, Optional MatchCase As Boolean = False) As
String
' Performs a batch of find/replace ops on a single string.
Dim strErrMsg As String
If UBound(FindArray) - LBound(FindArray) < UBound(ReplaceArray) -
LBound(ReplaceArray) Then
GoTo errUnequalArrays
End If

On Error GoTo errBadReplace
Dim i As Integer
For i = LBound(FindArray) To UBound(FindArray)
If MatchCase Then
InputString = Replace(InputString, FindArray(i),
ReplaceArray(i), , , vbBinaryCompare)
Else
InputString = Replace(InputString, FindArray(i),
ReplaceArray(i), , , vbTextCompare)
End If
Next i

BatchReplace = InputString

Exit Function

errUnequalArrays:
strErrMsg = "Error. The number of entries in the FindArray and
ReplaceArray do not match."
Err.Raise Number:=vbObjectError + 1000, source:="BatchReplace",
Description:=strErrMsg
Exit Function

errBadReplace:
strErrMsg = "Error. An unknown error occurred during the
replacement operations."
Err.Raise Number:=vbObjectError + 1001, source:="BatchReplace",
Description:=strErrMsg
Exit Function

End Function