I recently needed a substring replacement function for inserting code into a module, by reading the code from a file. Unfortunately, in my case, commas are interpreted as delimiters, and the insertion requires a lot of post formatting. So, I replaced all the commas in the original file with a question mark. That way, when the file is inserted into a module, the ReplaceString function checks each line, and the question mark is replaced with a comma, then inserted into the module. I initially considered using the fConvert function published in "Remove Unwanted Characters" ["101 Tech Tips for VB Developers," Supplement to VBPJ, February 1999]. I compared the speed of the two functions, ReplaceString and fConvert, in a separate project, using the Windows API GetTickCount function. The recursive function is nearly four times faster than the For...Loop. In situations where a single character needs to be replaced with something different, it's a good way to go:
Public Function ReplaceString(strT As String) As String
Dim iposn As Integer
Dim strF As String
Dim strR As String
' Function replaces one character with another. Using
' recursion if the character is found to check if any
' more such characters need to be replaced within
' the string. strT is the string in which a character
' or string in which replacement will take place.
' strF is the string which is to be replaced.
' strR the new or replacing string.
strF = "?"
strR = ","
iposn = InStr(1, strT, strF)
If iposn > 0 Then
Mid(strT, iposn, 1) = strR
strT = ReplaceString(strT)
ReplaceString = strT