Split function compare options

[JenniferMurphy]

I'm a bit puzzled by a couple of the Compare options for the Split function.

According to this link:

https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/split-function

The Compare option has these values:

Value	Variable	Description
-1	vbUseCompareOption	Performs a comparison using the setting of the Option Compare statement.
0	vbBinaryCompare	Performs a binary comparison. Case sensitive? Default?
1	vbTextCompare	Performs a textual comparison. Case insensitive?
2	vbDatabaseCompare	Microsoft Access only. Performs a comparison based on information in your database.

<tbody>
</tbody>

I tested these Compare settings using this expression:

A=Split("3.5ft","FT",,see table)

I got these results:

Compare Setting	UBound	A(0)	A(1)	Comments
omitted	0	|3.5ft|		Case sensitive?
-1	--	--		Invalid call
vbUseCompareOption	0	|3.5ft|		Case sensitive?
0	0	|3.5ft|		Case sensitive?
vbBinaryCompare	0	|3.5ft|		Case sensitive?
1	1	|3.5|	||	Case insensitive?
vbTextCompare	1	|3.5|	||	Case insensitive?
2	1	|3.5|	||	Case insensitive?
vbDatabaseCompare	1	|3.5|	||	Case insensitive?
3	1	|3.5|	||	Case insensitive?

<tbody>
</tbody>

Questions:

1. Which one is the default? It appears to be the binary compare (1).
2. Why do I get an error when I explicitly code "-1", but not when I code vbUseCompareOption, which = -1?
3. Is the binary compare case sensitive and the text compare case insensitive?
4. It appears that any value > 1 works the same as 1.

 

[JenniferMurphy]

I found the answer to #2 . The website is wrong. the value of vbUseCompareOption is null, not -1.

 

[John_w]

The default is vbBinaryCompare. This is shown in the function arguments when you type "Split(".

vbBinaryCompare is case sensitive and vbTextCompare is case insensitive.

The same rules apply to other string functions such as Instr and Replace.

 

[JenniferMurphy]

The default is vbBinaryCompare. This is shown in the function arguments when you type "Split(".

Ah, good point.

vbBinaryCompare is case sensitive and vbTextCompare is case insensitive.

The same rules apply to other string functions such as Instr and Replace.

Thanks

 

[JenniferMurphy]

The default is vbBinaryCompare. This is shown in the function arguments when you type "Split(".

It appears that this method, like the help files, is not perfect. At least on my VB6 system, the argument prompt does not show any default value for the second argument (the delimiter). The default is the space character.

I would think that as long as M$FT has been in business and as much money they have made off of everyone, they could at least fix some of the most obvious and simplest bugs.

 

[Rick Rothstein]

At least on my VB6 system, the argument prompt does not show any default value for the second argument (the delimiter). The default is the space character.

I would think that as long as M$FT has been in business and as much money they have made off of everyone, they could at least fix some of the most obvious and simplest bugs.

It is covered in the Help file for the Split function.

 

[JenniferMurphy]

It is covered in the Help file for the Split function.

Yes. My point was that some of the defaults are covered in the help file and some in the argument prompt but neither source covers them all. Pretty typical for M$FT.