JenniferMurphy
Well-known Member
- Joined
- Jul 23, 2011
- Messages
- 2,691
- Office Version
- 365
- Platform
- Windows
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:
[TABLE="class: grid, width: 700"]
<tbody>[TR]
[TD="align: center"]Value
[/TD]
[TD="align: center"]Variable
[/TD]
[TD="align: center"]Description[/TD]
[/TR]
[TR]
[TD="align: center"]-1[/TD]
[TD]vbUseCompareOption[/TD]
[TD]Performs a comparison using the setting of the Option Compare statement.[/TD]
[/TR]
[TR]
[TD="align: center"]0[/TD]
[TD]vbBinaryCompare[/TD]
[TD]Performs a binary comparison. Case sensitive? Default?[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]vbTextCompare[/TD]
[TD]Performs a textual comparison. Case insensitive?[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]vbDatabaseCompare[/TD]
[TD]Microsoft Access only. Performs a comparison based on information in your database.[/TD]
[/TR]
</tbody>[/TABLE]
I tested these Compare settings using this expression:
I got these results:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]Compare Setting[/TD]
[TD="align: center"]UBound[/TD]
[TD="align: center"]A(0)[/TD]
[TD="align: center"]A(1)[/TD]
[TD]Comments[/TD]
[/TR]
[TR]
[TD="align: center"]omitted[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]|3.5ft|[/TD]
[TD="align: center"][/TD]
[TD]Case sensitive?[/TD]
[/TR]
[TR]
[TD="align: center"]-1[/TD]
[TD="align: center"]--[/TD]
[TD="align: center"]--[/TD]
[TD="align: center"][/TD]
[TD]Invalid call[/TD]
[/TR]
[TR]
[TD="align: center"]vbUseCompareOption[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]|3.5ft|[/TD]
[TD="align: center"][/TD]
[TD]Case sensitive?[/TD]
[/TR]
[TR]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]|3.5ft|[/TD]
[TD="align: center"][/TD]
[TD]Case sensitive?[/TD]
[/TR]
[TR]
[TD="align: center"]vbBinaryCompare[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]|3.5ft|[/TD]
[TD="align: center"][/TD]
[TD]Case sensitive?[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]|3.5|[/TD]
[TD="align: center"]||[/TD]
[TD]Case insensitive?[/TD]
[/TR]
[TR]
[TD="align: center"]vbTextCompare[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]|3.5|[/TD]
[TD="align: center"]||[/TD]
[TD]Case insensitive?[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]|3.5|[/TD]
[TD="align: center"]||[/TD]
[TD]Case insensitive?[/TD]
[/TR]
[TR]
[TD="align: center"]vbDatabaseCompare[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]|3.5|[/TD]
[TD="align: center"]||[/TD]
[TD]Case insensitive?[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]|3.5|[/TD]
[TD="align: center"]||[/TD]
[TD]Case insensitive?[/TD]
[/TR]
</tbody>[/TABLE]
Questions:
According to this link:
https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/split-function
The Compare option has these values:
[TABLE="class: grid, width: 700"]
<tbody>[TR]
[TD="align: center"]Value
[/TD]
[TD="align: center"]Variable
[/TD]
[TD="align: center"]Description[/TD]
[/TR]
[TR]
[TD="align: center"]-1[/TD]
[TD]vbUseCompareOption[/TD]
[TD]Performs a comparison using the setting of the Option Compare statement.[/TD]
[/TR]
[TR]
[TD="align: center"]0[/TD]
[TD]vbBinaryCompare[/TD]
[TD]Performs a binary comparison. Case sensitive? Default?[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]vbTextCompare[/TD]
[TD]Performs a textual comparison. Case insensitive?[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]vbDatabaseCompare[/TD]
[TD]Microsoft Access only. Performs a comparison based on information in your database.[/TD]
[/TR]
</tbody>[/TABLE]
I tested these Compare settings using this expression:
Code:
A=Split("3.5ft","FT",,see table)
I got these results:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]Compare Setting[/TD]
[TD="align: center"]UBound[/TD]
[TD="align: center"]A(0)[/TD]
[TD="align: center"]A(1)[/TD]
[TD]Comments[/TD]
[/TR]
[TR]
[TD="align: center"]omitted[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]|3.5ft|[/TD]
[TD="align: center"][/TD]
[TD]Case sensitive?[/TD]
[/TR]
[TR]
[TD="align: center"]-1[/TD]
[TD="align: center"]--[/TD]
[TD="align: center"]--[/TD]
[TD="align: center"][/TD]
[TD]Invalid call[/TD]
[/TR]
[TR]
[TD="align: center"]vbUseCompareOption[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]|3.5ft|[/TD]
[TD="align: center"][/TD]
[TD]Case sensitive?[/TD]
[/TR]
[TR]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]|3.5ft|[/TD]
[TD="align: center"][/TD]
[TD]Case sensitive?[/TD]
[/TR]
[TR]
[TD="align: center"]vbBinaryCompare[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]|3.5ft|[/TD]
[TD="align: center"][/TD]
[TD]Case sensitive?[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]|3.5|[/TD]
[TD="align: center"]||[/TD]
[TD]Case insensitive?[/TD]
[/TR]
[TR]
[TD="align: center"]vbTextCompare[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]|3.5|[/TD]
[TD="align: center"]||[/TD]
[TD]Case insensitive?[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]|3.5|[/TD]
[TD="align: center"]||[/TD]
[TD]Case insensitive?[/TD]
[/TR]
[TR]
[TD="align: center"]vbDatabaseCompare[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]|3.5|[/TD]
[TD="align: center"]||[/TD]
[TD]Case insensitive?[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]|3.5|[/TD]
[TD="align: center"]||[/TD]
[TD]Case insensitive?[/TD]
[/TR]
</tbody>[/TABLE]
Questions:
- Which one is the default? It appears to be the binary compare (1).
- Why do I get an error when I explicitly code "-1", but not when I code vbUseCompareOption, which = -1?
- Is the binary compare case sensitive and the text compare case insensitive?
- It appears that any value > 1 works the same as 1.