Batch Replace function for Large strings

[R Avery]

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

 

[Tom Ogilvy]

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)

 

[Peter T]

As Tom suggested it would be quicker to do on the worksheet, however if
strings are longer than about 900, I find Edit Search & Replace complains
that the formula is too long.

With just a quick look at your code, if not all FindArray strings exist in
InputString you could avoid unnecessary and time consuming Replace's by
first checking:

If InStr(1, InputString, FindArray(i)) Then

There's probably some cut off where using this test increases time, in other
words if most FindArray strings will be found, and a total waste of time if
all 50 will be found & need replacing.

Regards,
Peter T

 

[R Avery]

I'm sorry, I must have been unclear. I am not referring to Excel
strings, I am strictly referring to VBA strings that are multi-megabyte
strings and are therefore extremely difficult to handle with speed.

 

[Peter T]

I'm sorry, I must have been unclear. I am not referring to Excel

strings, I am strictly referring to VBA strings that are multi-megabyte
strings and are therefore extremely difficult to handle with speed.

Have you tried automating Word and using its Find / Replacement.Text method.
Though I expect there are probably better ways.

Don't think most people reading your "large strings" would have imagined
what you were dealing with. Might be worth reposting with subject something
like:
Find & Replace in Multi-Megabyte Strings

Regards,
Peter T