String Replace, performance issues?

[Maury Markowitz]

I've read in a few places that the Replace function is slow, and is a
potential performance problem. I have a function that is called
thousands of times during one lengthy operation...

Public Function CleanString(aString)
temp = aString

temp = Replace(temp, " ", "")
temp = Replace(temp, vbTab, "")
temp = Replace(temp, vbCrLf, "")
temp = Replace(temp, vbCr, "")
temp = Replace(temp, vbLf, "")
temp = Replace(temp, ";", "")
temp = Replace(temp, ",", "")
temp = Trim(temp)

CleanString = temp
End Function

Is there a better way to do this? Should I use instr to test before
replacing? Is there some sort of "replace anything in this list
with...". Anything else I should consider?

Maury

 

[Douglas J. Steele]

Unfortunately, there's no built-in "replace anything in this list with..."
function. And I'm not sure that writing your own function would solve
anything, as I'd like to think that the built-in functions are going to be
as efficient as anything you could write.


I don't believe there would be any benefit in using InStr first. If your
strings very seldom contain those symbols, it might be faster, but in all
other cases, you'd be doing the search for each special character twice.

One comment I'd make is that it's not necessary to check for vbCrLf, vbCr
and vbLf. vbCrLf is the concatenation of vbCr and vbLf, so

temp = Replace(temp, vbCr, "")
temp = Replace(temp, vbLf, "")

will also do

temp = Replace(temp, vbCrLf, "")

 

[fredg]

I've read in a few places that the Replace function is slow, and is a
potential performance problem. I have a function that is called
thousands of times during one lengthy operation...

Public Function CleanString(aString)
temp = aString

temp = Replace(temp, " ", "")
temp = Replace(temp, vbTab, "")
temp = Replace(temp, vbCrLf, "")
temp = Replace(temp, vbCr, "")
temp = Replace(temp, vbLf, "")
temp = Replace(temp, ";", "")
temp = Replace(temp, ",", "")
temp = Trim(temp)

CleanString = temp
End Function

Is there a better way to do this? Should I use instr to test before
replacing? Is there some sort of "replace anything in this list
with...". Anything else I should consider?

Maury

Perhaps this method will run faster.

Temp = Replace(Replace(Replace(Replace(Replace(Replace([Temp],"
",""),vbTab,""),vbCr,""),vbLf,""),";",""),",","")

 

[RoyVidar]

I've read in a few places that the Replace function is slow, and is a
potential performance problem. I have a function that is called
thousands of times during one lengthy operation...

Public Function CleanString(aString)
temp = aString

temp = Replace(temp, " ", "")
temp = Replace(temp, vbTab, "")
temp = Replace(temp, vbCrLf, "")
temp = Replace(temp, vbCr, "")
temp = Replace(temp, vbLf, "")
temp = Replace(temp, ";", "")
temp = Replace(temp, ",", "")
temp = Trim(temp)

CleanString = temp
End Function

Is there a better way to do this? Should I use instr to test before
replacing? Is there some sort of "replace anything in this list
with...". Anything else I should consider?

Maury

There is some sort of "replace anything in this list with..." function
available through Regular Expressions.

Whether it proves more effective, I don't know, but perhaps you can
try it?

It has some more overhead than ordinary string matching/replacing
functions, but since you can replace more than one match at the time,
it might perhaps be faster. And, while the ordinary functions often
will be slower and slower when the length of the text increases,
Regular Expressions are less influenced by that.

Now this is untested, but, as at the top of the module, declare a
module level variable

Private mre as Object

' then a function

Pulic Function Public Function CleanString(aString)

If mre Is Nothing Then
' instantiate only first time
Set mre = CreateObject("vbscript.regexp")
End If
With mre
.Global = True
.Pattern = "[ ,;\n\n\r]"

' \t - tab
' \n - new line
' \r - carriage return

CleanString = .Replace(aString, "")
End With

End Function

 

[Dirk Goldgar]

I've read in a few places that the Replace function is slow, and is a
potential performance problem. I have a function that is called
thousands of times during one lengthy operation...

Public Function CleanString(aString)
temp = aString

temp = Replace(temp, " ", "")
temp = Replace(temp, vbTab, "")
temp = Replace(temp, vbCrLf, "")
temp = Replace(temp, vbCr, "")
temp = Replace(temp, vbLf, "")
temp = Replace(temp, ";", "")
temp = Replace(temp, ",", "")
temp = Trim(temp)

CleanString = temp
End Function

Is there a better way to do this? Should I use instr to test before
replacing? Is there some sort of "replace anything in this list
with...". Anything else I should consider?

You could try something like this (adapted as needed) and see if it performs
better or worse:

'----- start of code -----
Function CleanString2(SourceString As String) As String

Dim lngPos As Long
Dim lngOutX As Long
Dim strChar As String
Dim strOut As String

strOut = Space$(Len(SourceString))

For lngPos = 1 To Len(SourceString)
strChar = Mid$(SourceString, lngPos, 1)
If strChar Like "[A-Za-z0-9]" Then
lngOutX = lngOutX + 1
Mid(strOut, lngOutX) = strChar
End If
Next lngPos

CleanString2 = Left$(strOut, lngOutX)

End Function

'----- end of code -----

Note that the "Like" expression is intended to identify the characters that
are to be kept in the output string. You could turn the logic around and
check to see if the current character is in the no-no list, if you want.

 

[Maury Markowitz]

Ok, so apparently the scoop is that Replace does an InStr internally.
If you Replace on a lot of strings that DON'T contain the string in
question it runs much faster than the same set when the hits are in
there. Very interesting.

I'm going to try the regex too.

Maury