Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Batch Replace function for Large strings
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find and replace numeric strings in larger text strings | Excel Worksheet Functions | |||
run batch job from each row - how to insert run batch function per row? | Excel Discussion (Misc queries) | |||
hash function for large strings | Excel Worksheet Functions | |||
Search and Replace text strings within Macros | Excel Programming |