Glitch in Replace function / Object Browser documentation

[AHeyne]

I have accidentally noticed a behavior of the String.Replace function that does not match the documentation from the Object Browser.

According to the Object Browser, the default value of the optional parameter 'Compare' is 'vbBinaryCompare':

However, the actual behavior is not like this. Here is a recording from the Immediate Window:

?Replace("A", "a", "x", , ,vbTextCompare) = "x"
True

?Replace("A", "a", "x", , ,vbBinaryCompare) = "x"
False

?Replace("A", "a", "x") = "x"
True

Without specifying the 'Compare' parameter, the result should actually be 'False'.

Is that the same for you?

I am using Access 2016 including all updates. VBA is version 7.1.1146

 

[Josef P.]

Replace without compare parameter depends on the “Option Compare” setting.

Option Compare Database ' OR Text
Option Explicit

Private Sub Test()
   Debug.Print Replace("A", "a", "x") = "x"
End Sub

vs.

Option Compare Binary
Option Explicit

Private Sub Test()
   Debug.Print Replace("A", "a", "x") = "x"
End Sub

 

[arnelgp]

regardless of what Option Compare is set, the
documentation of the Default for Compare method is vbBinaryCompare.
so the documentation is wrongly True.

 

[AHeyne]

Thanks for testing @Josef P. and @arnelgp .

So yes, finally the Object Browser 'documentation' is wrong.
The online documentation doesn't mention a default value of the 'compare' parameter at all.

 

[Josef P.]

Correct would be (online documentation + behavior in VBA):

Function Replace(Expression As String, Find As String, Replace As String, [Start As Long = 1], [Count As Long = -1], _
                 [Compare As VbCompareMethod = vbUseCompareOption]) As String

vbUseCompareOption = -1 ... this value cannot be used as a parameter value in VBA

 

[cheekybuddha]

IIRC, in Excel the default is vbBinaryCompare

?Replace("hello", "H", "y")
hello
?Replace("hello", "h", "y")
yello

Access relies on the Option Compare if the mode is not explicitly specified.

 

[isladogs]

Agree that the VBE documentation isn't clear but:
a) there are actually 4 options for vbCompareMethod though vbUseCompareOption isn't listed in the VBE

Replace function (Visual Basic for Applications)

Office VBA reference topic

learn.microsoft.com

b) the default vbCompareMethod where not specified is vbBinaryCompare

c) However in Access, where Opton Compare Database is used, this takes precedence.

VBE Option Statements

This article outlines the various option statements that can be used at the start of code modules in the visual basic editor (VBE).

www.isladogs.co.uk