Bug in Replace function?

[Guest]

Hi,
I've a problem using the Replace function in VBA. I've the string "ab xx cd
xx efgh" and I want to replace the second "xx" with "yy". This should be done
with Replace:

newString = Replace(oldString, "xx", "yy", 10) 'start at pos 10

However, this results in newString = "yy efgh"

Is this is a bug, or is my interpretation of Replace wrong? What is then the
correct code?

Cheers,
Henk

 

[Guest]

Copied from VBA Help for Replace Function:
The return value of the Replace function is a string, with substitutions
made, that begins at the position specified by start and and concludes at the
end of the expression string. It is not a copy of the original string from
start to finish.

 

[Guest]

OK, I see it.
But is not just the way I would expect the Replace should work, for this is
in fact "Replace and Cut-off"

 

[Guest]

I agree, but that's the way it is. Now, I'm sure you can accomplish what you
want to do. It is a matter of knowing the business rules. If the rule is
you only want to change the second occurance, then you can do something like
this:
'Replace all occurances
oldString = Replace(oldString, "xx", "yy")
'Change the first occurance back to xx
newString = Replace(oldString, "xx", "yy", ,1)
'Which will replace only the first occurance and return the entire string.

 

[Peter R. Fletcher]

Assuming, of course, that neither "xx" nor "yy" can validly occur
elsewhere in the code!

Safer would be to use Mid$ to split the string into two halves at the
relevant position, check for and do the replacement, and then put the
string back together again.

I agree, but that's the way it is. Now, I'm sure you can accomplish what you
want to do. It is a matter of knowing the business rules. If the rule is
you only want to change the second occurance, then you can do something like
this:
'Replace all occurances
oldString = Replace(oldString, "xx", "yy")
'Change the first occurance back to xx
newString = Replace(oldString, "xx", "yy", ,1)
'Which will replace only the first occurance and return the entire string.

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher

 

[coasterman]

Replace is probably the wrong tool fo rthe job you are trying to do.

If the second occurrence of 'xx' is always 'xx' in position 10 then you would
be advised to use Mid(). This depends on you replacing a known expression of
a known size at a known location with another known value.

Mid(oldString, 10,2) = "xy" - note you must use Mid() NOT Mid$()


If, however, as I suspect you, want to remove the 2nd occurrence of a known
string then the most efficient is to use Regular expressions. Here's a
function which'll allow you to replace the second occurrence of any sub-
string in any string, with something else.

Your example would use it like this:

oldString = "ab xx cd xx efgh"
lookFor = "xx"
changeto = "xy"
newString = Replace2nd(oldString , lookFor, changeto)

Function Replace2nd(strIn as string, _
strFind as string, _
strReplace as string _
) as string
Dim re as Object
Set re = CreateObject("VBScript.RegExp")
re.IgnoreCase = True 'alter as required, or change to use an additional
Optional parameter.
re.Pattern = "(.*)(" & strFind & ")(.*)( & strFind & )(.*)" ' Creates a
pattern of "(.*)(xx)(.*)(xx)(.*)"
If re.test(strIn ,"$1$2$3" & strReplace & "$5") Then
Replace2nd = re.Replace(strIn ,"$1$2$3" & strReplace & "$5")
Else
Replace2nd = strIn
End If
End Function
This has the added advantage that instances of the search string - "xx" in
this case - anywhere in the oldString - it will work even if the oldString
were just "xxxx".
Regular expressions are well worth the investment in time to learn as they
are by far the most efficient way to do complex string manipulation.

 

[Guest]

Had you actually read my post, you would know that it was based on an
assumption and qualified with having to know what the business rules are. I
doubt your solution would be any better without a complete understanding of
the objective and what the data may be.

 

[Guest]

Another nice solution I found with MVP Doug Steele. It does my job perfectly
and acts just as I would expect Replace to do. The comment says: " Provides
the same functionality as the Replace function which is included in Access
2000 and newer." But this function don't cut off!


Public Function MyReplace( _
ByVal ExpressionToSearch As Variant, _
ByVal FindString As String, _
ByVal ReplaceString As String, _
Optional ByVal Start As Long = 1, _
Optional ByVal MaxChanges As Long = -1, _
Optional ByVal CompareType As Long = vbBinaryCompare _
) As String

' This code was originally written by
' Doug Steele, MVP (e-mail address removed)
'
' You are free to use it in any application
' provided the copyright notice is left unchanged.
'
' Description: Provides the same functionality as the Replace
' function which is included in Access 2000 and newer.
'
' Inputs: ExpressionToSearch Required. String expression containing
' substring to replace.
' FindString Required. Substring being searched for.
' ReplaceString Required. Replacement substring.
' Start Optional. Position within expression where
' substring search is to begin.
' If omitted, 1 is assumed.
' MaxChanges Optional. Number of substring
substitutions
' to perform.
' If omitted, the default value
is -1,
' which means make all possible
' substitutions.
' CompareType Optional. Specifies the type of string
' comparison. Valid values are:
' vbBinaryCompare (0)
' vbTextCompare (1)
' vbDatabaseCompare (2)
' The default is to perform a
binary
' comparison.
' Specify vbTextCompare (1) to
perform
' a textual, noncase-sensitive
comparison.
' Specify vbDatabaseCompare (2)
to perform
' a comparison based on
information
' contained in the database.
'
' Returns: A string where all occurrences of FindString have been
replaced by
' ReplaceString. The following rules apply:
' If Replace returns
' ExpressionToSearch is zero-length Zero-length string ("")
' ExpressionToSearch is Null Zero-length string
' FindString is zero-length Copy of
ExpressionToSearch
' ReplaceString is zero-length Copy of
ExpressionToSearch with all
' occurrences of
FindString removed
' Start > Len(ExpressionToSearch) Zero-length string
' MaxChanges = 0 Copy of
ExpressionToSearch

On Error GoTo Err_MyReplace

Dim booContinue As Boolean
Dim lngCount As Long
Dim lngFindLength As Long
Dim lngReplaceLength As Long
Dim lngSearchFrom As Long
Dim lngPos As Long
Dim strToSearch As String

' Check to make sure that the input parameters make sense.

strToSearch = ExpressionToSearch & vbNullString
If Len(strToSearch) = 0 Then
booContinue = False
Else
If Len(FindString) = 0 Then
booContinue = False
Else
If MaxChanges = 0 Then
booContinue = False
Else
If Start > Len(strToSearch) Then
strToSearch = vbNullString
booContinue = False
Else
booContinue = True
End If
End If
End If
End If

' We've checked all the simple cases.
' Do the actual replacing (if necessary)

If booContinue Then
lngSearchFrom = Start
lngFindLength = Len(FindString)
lngReplaceLength = Len(ReplaceString)
lngPos = InStr(lngSearchFrom, strToSearch, FindString, CompareType)
Do While lngPos > 0
strToSearch = Left$(strToSearch, lngPos - 1) & ReplaceString & _
Mid$(strToSearch, lngPos + lngFindLength)
lngCount = lngCount + 1
If MaxChanges <> -1 And lngCount >= MaxChanges Then
Exit Do
End If
lngPos = InStr(lngPos + lngReplaceLength, strToSearch,
FindString, CompareType)
Loop
End If

End_MyReplace:
MyReplace = strToSearch
Exit Function

Err_MyReplace:
Err.Raise Err.Number, "Replace", Err.Description
Resume End_MyReplace

End Function

//-----------------------------------------------------------------------------------

 

[coasterman via AccessMonster.com]

The pattern statement in my post should have read:

re,Pattern = "(.*?)(" & strFind & ")(.*?)( "& strFind & ")(.*?)"

Whoops!

Replace is probably the wrong tool fo rthe job you are trying to do.

If the second occurrence of 'xx' is always 'xx' in position 10 then you would
be advised to use Mid(). This depends on you replacing a known expression of
a known size at a known location with another known value.

Mid(oldString, 10,2) = "xy" - note you must use Mid() NOT Mid$()

If, however, as I suspect you, want to remove the 2nd occurrence of a known
string then the most efficient is to use Regular expressions. Here's a
function which'll allow you to replace the second occurrence of any sub-
string in any string, with something else.

Your example would use it like this:

oldString = "ab xx cd xx efgh"
lookFor = "xx"
changeto = "xy"
newString = Replace2nd(oldString , lookFor, changeto)

Function Replace2nd(strIn as string, _
strFind as string, _
strReplace as string _
) as string
Dim re as Object
Set re = CreateObject("VBScript.RegExp")
re.IgnoreCase = True 'alter as required, or change to use an additional
Optional parameter.
re.Pattern = "(.*)(" & strFind & ")(.*)( & strFind & )(.*)" ' Creates a
pattern of "(.*)(xx)(.*)(xx)(.*)"
If re.test(strIn ,"$1$2$3" & strReplace & "$5") Then
Replace2nd = re.Replace(strIn ,"$1$2$3" & strReplace & "$5")
Else
Replace2nd = strIn
End If
End Function
This has the added advantage that instances of the search string - "xx" in
this case - anywhere in the oldString - it will work even if the oldString
were just "xxxx".
Regular expressions are well worth the investment in time to learn as they
are by far the most efficient way to do complex string manipulation.

Hi,
I've a problem using the Replace function in VBA. I've the string "ab xx cd

[quoted text clipped - 10 lines]

Cheers,
Henk

 

[Peter R. Fletcher]

I read your post quite carefully. Your "solution" makes a lot of
assumptions and falls down under a significant number of perfectly
feasible circumstances, some of which might not be predicted to be
relevant at the time it was first implemented. Mine only (and very
explicitly) assumes that the OP wants to replace one specified
character sequence with another if and only if it appears at a
specified location in the source string.

Don't be so sensitive! None of us is perfect.

Had you actually read my post, you would know that it was based on an
assumption and qualified with having to know what the business rules are. I
doubt your solution would be any better without a complete understanding of
the objective and what the data may be.

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher