VBA InStr Excel vs Access

Question

The default InStr for Access seems to be vbTextCompare and for Excel seems to be vbBinaryCompare.

Can anyone confirm and/or shed light on why two different Microsoft products are handling the same code differently.

Answer 1

TL;DR - In Access, the immediate window doesn't have a module-level Option Compare statement to honor, but defaults to a different default than Excel, and a different default to an Access module that doesn't include an Option Compare statement.. Jump to the Conclusions for the specifics.

The Option Compare statement has Intellisense that suggests that the value must be a member of VbCompareMethod:

• vbBinaryCompare = 0
• vbTextCompare = 1
• vbDatabaseCompare = 2 'Runtime error 5 if used in non-Access VBA

However, the Microsoft documentation for InStr goes further and specifies that the value of Compare may also be vbUseCompareOption = -1, but vbUseCompareOption isn't in the Object Browser, and resolves to an undeclared Empty in the Immediate window in both Excel and Access:

?IsEmpty(vbUseCompareOption)
True

Furthermore, trying to use a literal value of -1 as the Compare argument results in Runtime error 5: Invalid procedure call or argument, so clearly the documentation isn't for trusting.

And trying to use vbUseCompareOption in a module with Option Explicit set results in Compile Error: Variable not defined, so relaxing the Option Explicit rule, allows the following 2 lines to be equivalent:

strcomp("A", "a", vbUseCompareOption)
strcomp("A", "a", Empty)

The documentation also says that you can provide a locale ID, for example: 1033 for US-English, or 1036 for France-French. I don't know of any collation differences between English and French to test, but there may be some for various codepages, and it's worth noting that SQL Server collation isn't the same as Windows collation, so it seems likely that Access collation is not the same as Windows location either. See Collation Types.

It seems likely that VBA's vbTextCompare uses Windows (or a VBA-flavored) collation, and vbDatabaseCompare (and possibly providing a locale ID) performs an Access-flavored SQL collation. Your Access database has a collation property that can be read with CurrentDb.CollatingOrder, and only set when used as an argument to DBEngine.CompactDatabase. It seems likely that VBA uses that CollatingOrder Locale ID when performing a vbDatabaseCompare

Implicit Compare

But it's not clear what the default value is, so you're correct, Excel and Access exhibit different behaviors in the Immediate window, when omitting the Compare argument:

'Excel
?strcomp("A","a")
-1 

'Access
?strcomp("A","a")
0 

Explicit Compare

But they share identical results when the Compare argument is supplied with a value of vbBinaryCompare or vbTextCompare

'Excel
?strcomp("A","a",vbBinaryCompare)
-1 
?strcomp("A","a",vbTextCompare)
0 

'Access
?strcomp("A","a",vbBinaryCompare)
-1 
?strcomp("A","a",vbTextCompare)
0 

Now, let's see what happens with each of Access's Option Compare settings in a module with various settings for the Access module's Option Compare.

                                       Compare Not | Compare  | Compare | Compare
Statement                            | specified   | Database | Binary  | Text   | Excel
StrComp("A", "a")                    |     -1      |     0    |   -1    |   0    |  -1
StrComp("A", "a", Empty)             |     -1      |    -1    |   -1    |  -1    |  -1
StrComp("A", "a", vbDatabaseCompare) |      0      |     0    |    0    |   0    | Error
StrComp("A", "a", vbBinaryCompare)   |     -1      |    -1    |   -1    |  -1    |  -1
StrComp("A", "a", vbTextCompare)     |      0      |     0    |    0    |   0    |   0

Observations

• When a Compare argument is not passed to StrComp, the Access results are consistent with Excel, only when Option Compare either isn't specified, or is specified as Option Compare Binary
• When a Compare argument is not passed to StrComp, the Access results are inconsistent with Excel, as we'd expect, only when Access' Option Compare is set as either Option Compare Text or Option Compare Database
• When a Compare argument is passed to StrComp, the results are consistent across Access and Excel, with the exception of the Error when vbDatabaseCompare is used with Excel.
• When a Compare argument value of Empty is passed to StrComp, the result is consistent with an implicit default of vbBinaryCompare. Yet another argument for using Option Explicit, as passing an undeclared/unassigned variable will potentially produce unexpected results.
• The results in Access are different between code executed in the Immediate Window and code executed in a Module with an Option Compare statement.

Conclusions

• In Access, code evaluated in the Immediate Window that omits the Compare argument in a call to StrComp, will default to Text Compare.
• In Access, code executed from within a module that omits the Compare argument in a call to StrComp, will default to the Option Compare setting in that module. If an Option Compare statement isn't present, the module will default to Binary, just like Excel.
• In non-Access VBA hosts (such as Excel), all code that omits an optional Compare argument, will default to Binary Compare.