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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Batch Replace function for Large strings
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Batch Replace function for Large strings
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" 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Batch Replace function for Large strings
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Batch Replace function for Large strings
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 |
Reply |
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 |