ListBox sort not as expected 3

[HughLerwill]

The following is giving me a mad morning;

List1.Sorted = True set at design time

Private Sub Command2_Click()

    List1.AddItem "TEKTIPS / MALTON"
    List1.AddItem "TEK-TIPS / MENWITH HILL"
    List1.AddItem "TEKTIPS / MEXBOROUGH"
    List1.AddItem "TEK-TIPS / NAFFERTON"
    List1.AddItem "TEK-TIPS / AMBERLEY"
    List1.AddItem "TEKTIPS / ARUNDEL"

End Sub

The resulting sort is not as expected, the "-" characters seem to be ignored. Results are as expected if I change the "-" characters for another e.g. ".".

Thanks in advance for any help.

 

[strongm]

The problem is that the sort algorithm (CompareString) used by the listbox is doing what Microsoft call a "word sort", in which a hyphen is considered a soft character, and thus is ignored (and remains unaffected by Option Sort Order). Apostrophes are also considered soft. Whilst we can change this when calling the API directly via the SORT_STRINGSORT this is not possible for a VB listbox.

I am not sure of an easy workaround for this (the ANSI nature of the VB GUI makes it somewhat difficult to use Unicode's hard hyphen, which would have been the easiest solution).

 

[HughLerwill]

Thanks strongm. Yes a little googling since I posted turned up similar but less detailed explanations. An unpleasant little feature.
I've resorted to placing the items into a string array which I can sort the way I like (using StrComp with the Binary Compare option), after which I squirt that into an unsorted listbox. It working fine and does'nt seem appreciably slower.
Thanks again for the quick response.

 

[TysonLPrice]

I'm not sure if this is what you are looking for but I got it from here:

http://www.vbforums.com/showthread.php?279157-sorting-a-listbox-runtime

Private Sub Command1_Click()

    Dim strSave As String
    Dim bSwapped As Boolean
    Dim lngIndex As Long
        
    bSwapped = True
    Do Until bSwapped = False
        bSwapped = False
        For lngIndex = 1 To list1.ListCount - 1
            ' Change the "<" to a ">" to reverse the order of the sort
            If list1.List(lngIndex) < list1.List(lngIndex - 1) Then
                strSave = list1.List(lngIndex)
                list1.List(lngIndex) = list1.List(lngIndex - 1)
                list1.List(lngIndex - 1) = strSave
                bSwapped = True
                Exit For
            End If
        Next
    Loop

End Sub

You can call it after the listbox is populated.

 

[HughLerwill]

Thanks Tyson, I had considered doing it that way but I have a long list of items and I expect that sorting the strings without the overhead of the listbox control will be faster.

 

[dilettante]

Even with a Unicode control (such as VB6's Windowless Listbox) there is no free lunch. The "hyphens" (&H2010-&H2015) are all ignored for sorting. You'd have to use "Box Drawing Light Horizonal" &H2500 or something to make them significant.

To use something like CompareString/SORT_STRINGSORT you need an owner-drawn listbox without strings as far as I know.

 

[HughLerwill]

dil.. - thanks
>The "hyphens" (&H2010-&H2015)...
can you translate that into 'U+nnnn' speak for me because I currently use charmap.exe for such investigations; or is there a better way?

 

[dilettante]

The "U+" notation is followed by a value in hexadecimal. If you don't know that I'm not sure how you were planning to use the values. But of course that also assumes you know the difference between Chr$() and ChrW$().

Note: Using those values will not help, the default "alphabetical" sort treats them just like a regular hyphen/dash.


Your best bet is sorting them yourself. Most of the time these aren't enormous lists anyway, and a simple minded collection-based insertion technique works just fine:

Option Explicit

Private Sub Insert(ByVal ListBox As ListBox, ByVal Item As String)
    Dim I As Integer
    
    With ListBox
        For I = 0 To .ListCount - 1
            If Item < .List(I) Then
                .AddItem Item, I
                Exit Sub
            End If
        Next
        .AddItem Item
    End With
End Sub

Private Sub Command1_Click()
    Dim I As Integer
    Dim S As String
    Dim J As Integer
    
    'Make some strings and insert them into the list in oder:
    For I = 1 To 500
        S = Space$(Fix(Rnd() * 40) + 10)
        For J = 1 To Len(S)
            If J > 1 And J < Len(S) And Fix(Rnd() * 20) = 0 Then
                Mid$(S, J, 1) = "-" 'Occasionally insert a hyphen.
            Else
                Mid$(S, J, 1) = Chr$(Fix(Rnd() * 26) + 65) 'A through Z.
            End If
        Next
        Insert List1, S
    Next
    'Insert these additional sample cases:
    Insert List1, "TEKTIPS / MALTON"
    Insert List1, "TEK-TIPS / MENWITH HILL"
    Insert List1, "TEKTIPS / MEXBOROUGH"
    Insert List1, "TEK-TIPS / NAFFERTON"
    Insert List1, "TEK-TIPS / AMBERLEY"
    Insert List1, "TEKTIPS / ARUNDEL"
End Sub

 

[dilettante]

I love typos <sarcasm>. "Insert them in odor" indeed.

 

[strongm]

>The "hyphens" (&H2010-&H2015) are all ignored for sorting

Oh well, so much for that idea. I made a foolish assumption.

>you need an owner-drawn listbox

Yep,as I said, no easy workaround here.

The 'odor' solution seems the cheapest alternative

 

[HughLerwill]

dil.. Thanks for the tutorial on the U+s
Thanks too for Sub Insert, a slight change;

If ucase$(Item) < ucase$(.List(I)) Then

seems to keep the list alphabetical regardless of the case of the new entry, which is what I want and which is where the binary sort approach let me down.
The odor of success...

 

[TysonLPrice]

I love typos <sarcasm>. "Insert them in odor" indeed."

We have a Doctor on file named Richard Oder. I'll bet he got teased in school

 

[strongm]

>where the binary sort approach let me down

You could fix that by using CompareString with the SORT_STRINGSORT flag, as previously mentioned, rather than StrComp

 

[HughLerwill]

>You could fix that ..
Yes that occurred to me just now and I modified the comparison in my sort routine to

If StrComp(UCase$(MyArray$(s4)), UCase$(MyArray$(s4 + s)), vbBinaryCompare) < 1 Then

which also seems to be having the desired effect.

>The CompareString API with the SORT_STRINGSORT flag..
looks interesting and I'll be reading up on it.

Thanks.

 

[dilettante]

I was expecting "hard hyphen" to work myself, then I tried it.

Shlwapi.dll also offers a couple of compares: StrCmp() and StrCmpI(), also StrCmpLogicalW() for numeric strings which places "2AA" before (less than) "20A" for example.

 

[HughLerwill]

Given that a custom sort is required because that offered by the ListBox is unsuitable, here are some relative timings for some different approaches/ methods;
In my tests the data is obtained from a Source array of about 11,500 records with about 6700 unique entries, each entry can be up to 30 characters long, trailing spaces trimmed.
To ensure unique entries in the final list a Collection is used as follows; (this has been found quicker than using a binary search of existing entries or SendMessage(.hWnd, LB_FINDSTRINGEXACT, -1, ByVal a$)

For i = 1 To UBound(SourceArray)
            a$ = SourceArray(i)
            On Error Resume Next
            col.Add vbNullChar, UCase$(a$)
            If Err.Number = 0 Then (typically) List1.AddItem a$ 'this line varies depending on the sort method being used 
            On Error GoTo 0
    Next

Method 0 the reference - Use a Sorted ListBox as in the code above - 0.34s (the sort is flawed as previosly discussed)
An unsorted ListBox is used and a custom sort of some kind is applied in all the following
Method 1 - Add all items and then sort them in situ in the ListBox - 17s
Method 2 - Insert items into the Collection in order via a binary search to determine the insertion point, then add/ loop them into the ListBox in order - 3.94s
Method 3 - Insert items into the ListBox in order via a binary search to determine the insertion point - 0.53s
Method 4 - Add all items into a String array, sort them using a QuickSort routine, then add/ loop them into the ListBox in order - 0.38s

Results are very similar on XP single core 2.26GHz (IDE and exe) and on a Win7 i7 Quad core 1.73GHz in the IDE, timings for exes on the Win7 machine however are significantly slower for me.
Any feedback after comparing speed of the following (just straight forward .AddItem with no sorting) in IDE vs exe in W7 would be appreciated;

Option Explicit

Private Declare Function QueryPerformanceFrequency Lib "kernel32" (lpFrequency As Currency) As Long
Private Declare Function QueryPerformanceCounter Lib "kernel32" (lpPerformanceCount As Currency) As Long

Private Sub Command7_Click()

    Dim i&, j&, QPTicks As Currency, QPTicks2 As Currency, QPfreq As Currency
    'List1 is set unsorted at design time
    
    QueryPerformanceFrequency QPfreq
    
    List1.Visible = False
    QueryPerformanceCounter QPTicks
    For j = 1 To 100
        List1.Clear
        For i = 1 To 1000
                List1.AddItem CStr(i)
        Next
    Next
    QueryPerformanceCounter QPTicks2
    List1.Visible = True
    
    MsgBox j - 1 & "*" & List1.ListCount & " Items in " & (QPTicks2 - QPTicks) / QPfreq & " seconds"
    
End Sub

 

[HughLerwill]

>on the Win7 machine
that's 64 bit

 

[dilettante]

I'd suggest that stuffing 10,000 items into a ListBox means you ought to rethink your user interface. Imagine trying to pick an item, or worse yet do multiselection of 3 out of that list?

If you're stuffing in more than 100 it is time to rethink things. Even 100 is quite a few. At practical scales I'd use the method requiring the least and most simple code, if only to reduce the potential for subtle logic errors. Peformance differences are unlikely to matter.

BTW: The ListBox's built in sorting really isn't flawed, but designed to fit the normal use cases for ListBox controls. It doesn't meet every need but it was implemented as it on purpose.

 

[HughLerwill]

dil.. thanks. Yes I know the list is a big one but it's the basis of an autocomplete text box, showing previous entries in a database field. So I begin typing 'tek' and all the previos entries begining with 'tek' are displayed in a ListBox below the TextBox. If the number of entries Like 'tek*' only amount to say 5 then the ListBox just has five rows showing them all, if it has more then more rows are shown upto a predetermined limit say 35. It works very well. Just curious why it's much faster in the IDE than an exe in Win7 (64 bit).
Or may be you can tell me how to use SHAutoComplete Lib "shlwapi.dll" with a custom list.

 

[dilettante]

I don't think that helps unless you are after file paths or URLs.

Maybe look at AutoComplete/Auto DropDown Combo Box if you haven't yet. Might be something usable there.