vbUseCompareOption missing from Enum members of vbCompareMethod

Question

Many online, including Microsoft's, help resources (for example) for functions that accept a vbCompareMethod state that there are 4 possible values:

• vbUseCompareOption = -1 'Doesn't seem to exist
• vbBinaryCompare = 0
• vbTextCompare = 1
• vbDatabaseCompare = 2

But I've just checked Access 2000, 2007 and 2016, and none of them seem to list vbUseCompareOption in the Object Browser as being present. If I have Option Explicit set, I get a compile error saying that vbUseCompareOption is an undefined variable.

Does that mean that any VBA that uses the constant vbUseCompareOption, without Option Explicit, is potentially getting unexpected results?

Is this a throwback to an earlier version of VBA, or perhaps an Access peculiarity, or just a widely distributed error in the help?

Answer 1

It seems you are right, in that vbUseCompareOption does not exisit as a member of enumeration vbCompareMethod

So, what happens when you try and use it? As you found, when using Option Explicit you get a compile error, because a variable of that name is not defined.

If you don't use Option Explicit (usually a bad idea), a variable is created at run time, of type Variant, and value Empty. I ran an experiment to see what happens:

TL:DR - yes, you may get unexpected results (Binary Compare is always used)

I created two modules, one with Option Compare Binary the other with Option Compare text. I used StrComp as a test function that uses a Compare parameter.

Module one

'Option Explicit
Option Compare Text

Sub DemoText()
    Dim a As String, b As String
    a = "AAA"
    b = "aaa"
    Debug.Print "Option Comapre Text"
    Debug.Print "Text", "Binary", "Omitted", "vbUseCompareOption"
    Debug.Print StrComp(a, b, vbTextCompare), StrComp(a, b, vbBinaryCompare), StrComp(a, b), StrComp(a, b, vbUseCompareOption)
End Sub

Module two

'Option Explicit
Option Compare Binary

Sub DemoBinary()
    Dim a As String, b As String
    a = "AAA"
    b = "aaa"
    Debug.Print "Option Comapre Binary"
    Debug.Print "Text", "Binary", "Omitted", "vbUseCompareOption"
    Debug.Print StrComp(a, b, vbTextCompare), StrComp(a, b, vbBinaryCompare), StrComp(a, b), StrComp(a, b, vbUseCompareOption)
End Sub

The test

Sub Demo()
    DemoText
    DemoBinary
End Sub

The Result

Option Comapre Text
Text          Binary        Omitted       vbUseCompareOption
 0            -1             0            -1 
Option Comapre Binary
Text          Binary        Omitted       vbUseCompareOption
 0            -1            -1            -1 

And if you assign a value of -1 to it, you get a run time error 5 "Invalid Procedure Call or argument"

So, vbUseCompareOption does not exist, creating it with a value of -1 (or just calling a function with Compare:=-1) as the help suggests, fails.

Letting VBA create it as a variable (with value Empty) has no effect. Binary compare is always applied.

AFAICT, the only way to use the Option Compare setting is to leave out the Compare parameter in a function call.

Answer 2

It's a very good question. I addressed it at the Access team.

That enum member seems to be a left-over from who-knows-where, has never been implemented, and will probably now be removed from the documentation - with some not so high priority.

So, to conclude, all mentions of vbUseCompareOption should simply be ignored.

Answer 3

Expanding on chris neilsen's post: If no Option Compare statement is specified:

Module Three

'Option Explicit

Sub DemoNoOptionCompare()
    Dim a As String, b As String
    a = "AAA"
    b = "aaa"
    Debug.Print "No 'Option Compare'"
    Debug.Print "Text", "Binary", "Omitted", "vbUseCompareOption"
    Debug.Print StrComp(a, b, vbTextCompare), StrComp(a, b, vbBinaryCompare), StrComp(a, b), StrComp(a, b, vbUseCompareOption)
End Sub

Sub Demo2()
    DemoText
    DemoBinary
    DemoNoOptionCompare
End Sub

The Result

Option Compare Text
Text          Binary        Omitted       vbUseCompareOption
 0            -1             0            -1 
Option Compare Binary
Text          Binary        Omitted       vbUseCompareOption
 0            -1            -1            -1 
No 'Option Compare'
Text          Binary        Omitted       vbUseCompareOption
 0            -1            -1            -1

So the default when omitting the compare argument is controlled by the Option Compare statement if it exists, otherwise the default is a binary comparison.