Batch Replace function for Large strings

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: R Avery (robert.avery_at_gmail.com)
Date: 03/23/05 

Date: 23 Mar 2005 07:39:13 -0800

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

Relevant Pages

  • Re: Replace Count
    ... If you want to know the number of replacements required then it would be faster to run through the string first using InString in a loop and modifying the Start parameter within the loop to each time point to the first character after the previously found substring. ... Firstly, in your code you are replacing multiple vbCr characters whereas most standard Windows .txt files use the character pair vbCrLf instead of just a vbCr character, so presumably you are working with a text file that does use just vbCr to signify a new line, of which of course there are many, but you do need to know which kind of text file you are working with. ... Dim OldPointer As Long, NewPointer As Long ...
    (microsoft.public.vb.general.discussion)
  • Re: What is considered to be good material for a trem sustain block?
    ... The vibration of the string is transferred through the saddle, bridge, ... The sound you hear is from the pickups, ... I replaced the potmetal block on a 80's MIJ ESP Strat ... replacements. ...
    (alt.guitar)
  • Re: Problem using $1 in substitution command
    ... It is a lot of different replacements that has to be done so I list them in a .txt ... parts of a string while replacing others. ... How can I keep the substitution command as general as I have it, ... perl PerlSubstituteProblem.pl ...
    (perl.beginners)
  • Re: SUBSTR() with replacement or lvalue performance issues
    ... substitutions having a lenght larger than the substituted IalsoIRC, ... action-at-distance references outstanding on the same string, ... Doing replacements that don't preserve length can have a performance ...
    (comp.lang.perl.misc)
  • Re: Variable length/precision formats?
    ... Yes, it's standard conforming. ... The first is a string in which replacements will occur. ... The second is a pattern which will be replaced. ...
    (comp.lang.fortran)