vbTextCompare in the Split() function

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15498
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

vbTextCompare in the Split() function

Post by ChrisGreaves »

I see numerous examples of the Split() function here and in pages across the web, but I remain stubbornly confused as to the value of the vbTextCompare parameter.

I read that vbTextCompare=vbText will cause comparisons to be made in a case-sensitive fashion, while the default vbTextCompare=vbBinary is driven by case-insensitive. (Or the other way around).

I understand case-sensitivity, especially when comparing alphabetic text strings.

But what has this to do with the Split() function?

I thought the Split() function's job was to split a single string into sub-strings according to a delimiter; not to compare strings.

I am usually playing with word-strings or number-strings, so my delimiters are commonly <space> or <comma> and the like.

Is the vbCompare option going to be effective if ever I specify an alphabetic delimiter - which I suppose gives me the option of splitting at "a" or "A", whichever comes first, or of splitting only at "a", or only at "A"?

If anyone has ever consciously set vbCompare in the Split() function I'd be interested to hear the thinking behind the decision.

Thank you
Chris
An expensive day out: Wallet and Grimace

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: vbTextCompare in the Split() function

Post by HansV »

In "real life" (duh) I only use Split with delimiters such as space, comma, colon, semicolon, or pipe. The Compare argument is irrelevant in such cases.
As you guessed, it only comes into play if you use one or more letters in the Delimiter argument.
For example:

Code: Select all

Sub Test()
    Dim MyString As String
    Dim Parts() As String
    MyString = "Chris-A-Leif-a-Rudi"
    Dim i As Long
    Parts = Split(Expression:=MyString, Delimiter:="-A-", Compare:=vbBinaryCompare)
    For i = 0 To UBound(Parts)
        Debug.Print Parts(i)
    Next i
End Sub
This results in

Chris
Leif-a-Rudi

But the following

Code: Select all

Sub Test()
    Dim MyString As String
    Dim Parts() As String
    MyString = "Chris-A-Leif-a-Rudi"
    Dim i As Long
    Parts = Split(Expression:=MyString, Delimiter:="-A-", Compare:=vbTextCompare)
    For i = 0 To UBound(Parts)
        Debug.Print Parts(i)
    Next i
End Sub
results in

Leif
Rudi
Chris
Best wishes,
Hans