Bubble Sort failure, Strcomp with binary compare

[Neal Zimm]

Hi All,
I have a bubble sort failing when
"a" seems to compare greater then "B"
in a binary text compare. There are notes
in the bubble sort loop below.

Other more 'complicated' data tests well, also
in the notes.

Help is appreciated.
Thanks,
Neal Zimm


Sub zBubble_Sort()
Dim CompareHow As VbCompareMethod

Dim Ix As Long
Dim Jx As Long
Dim Lo As Long, Hi As Long

Dim KeyAy As Variant
Dim sHold As String
Dim sInput As String
Dim sOutput As String

' All binary compares
' KeyAy = Array("a", "A") 'good, result is Aa
' KeyAy = Array("b", "B") 'good, result is Bb
' KeyAy = Array("b", "a", "A") 'good, result = Aab
' KeyAy = Array("B", "a", "A") 'NOPE, result = ABa

CompareHow = vbBinaryCompare
''CompareHow = vbTextCompare

KeyAy = Array("b", "a", "A", "B") 'crap, result = ABab expect AaBb ??
GoSub SortTest

'the above has a problem, see sort. Bummer is that
'the data below works as expected.

KeyAy = Array("z i m", "z i M", "z I m", "z I M", "Z I m", "Z I M")
GoSub SortTest
For Ix = Lo To Hi
sOutput = sOutput & KeyAy(Ix) & vbCr
Next Ix
MsgBox sOutput
Exit Sub

SortTest:
Lo = LBound(KeyAy)
Hi = UBound(KeyAy)

For Ix = Lo To Hi
sInput = sInput & KeyAy(Ix)
Next Ix

'sort ascending
For Ix = Lo To (Hi - 1)
For Jx = (Ix + 1) To Hi
'The failure is @ Ix=2 "a", it compares greater
' than Jx=4 of "B" WHY ??
If StrComp(KeyAy(Ix), KeyAy(Jx), CompareHow) = 1 Then
sHold = KeyAy(Jx)
KeyAy(Jx) = KeyAy(Ix)
KeyAy(Ix) = sHold
End If
Next Jx
Next Ix


For Ix = Lo To Hi
sOutput = sOutput & KeyAy(Ix)
Next Ix
MsgBox sInput & vbCr & vbCr & sOutput
sInput = ""
sOutput = ""
Return
End Sub

 

[Bob Phillips]

That is because a is greater than B in ASCII. IF you want otherwise you will
need to shift to a consistent form, UCase or LCase.

 

[Bernard Liengme]

In the SortTest sub use
If StrComp(UCase(KeyAy(Ix)), UCase(KeyAy(Jx)), CompareHow) = 1 Then

Note the use of UCASE
But A, B, a. B in A1:A4; In B1 enter =CODE(A1);copu down the column. Now do
you see why the sort works the way it does?
best wishes

 

[Neal Zimm]

Well Bob,
Crap, not at you, just the situation. If i wanted to use U and L case, I
wouldn't be bothering with the binary compare, the sort works just fine with
the text compare.

So if you want an apha sort in VBA, where case matters, do you have to
build your own ?

Thanks and be well.

 

[Neal Zimm]

Cher Bernard,
Merci.
Lcase and Ucase do me no good.

The sort works fine with option compare text, but I wanted "more".

If you need a case sensitive sort, (and I forgot about the ascii
collating sequence) what's the best way without writing to a worksheet and
sorting there?

thanks much.
Neal

 

[Peter T]

So why not use text compare instead of binary.

Regards,
Peter T

 

[Chip Pearson]

I have an implementation of QSort on my web site at
http://www.cpearson.com/Excel/QSort.htm . You can download the module
at http://www.cpearson.com/zips/modQSortInPlace.zip . It handles both
case sensitive and case insensitive sorts in either ascending or
descending order. QSort is much faster than a bubble sort. The code
can handle sorting both strings and numerics and it can handle sorting
only a subset of an array.

You would call QSortInPlace with code like

Sub AAA()
Dim Arr(1 To 5) As String
Dim N As Long
Arr(1) = "z"
Arr(2) = "A"
Arr(3) = "a"
Arr(4) = "Z"
Arr(5) = "m"
QSortInPlace InputArray:=Arr, CompareMode:=vbBinaryCompare
For N = 1 To 5
Debug.Print Arr(N)
Next N
End Sub

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com

 

[Neal Zimm]

Peter, I need to build a "complementary" string in order to get the
descending functionality to work. the opposite of a space is the "~"
character. The text compare method did not recogize it. I'll be posting the
working code I've just finished later today. Thanks.

 

[Neal Zimm]

As always, thanks much Chip, I'll be looking at your site later today.

I'll be posting the code I just finished, here in about 10 minutes. I
should know by now to look at your site first, but I wanted the knowledge in
building the sort that I wrote.

I'm kinda proud of it, it sorts an array on any or all of its columns , each
column can be ascending or descending, but you can be sure I'll check yours
out.

Thanks again.

 

[Peter T]

Not sure what you mean by - the opposite of a space is the "~", however
StrComp with textCompare certainly does recognize the "~", indeed in this
range same as Excel's compare order (which is not entirely the same as
VB's).

Regards,
Peter T