Array Sort

Topic

New Reply

How does one sort the contents of a Word 97 combobox once the it’s populated?

Reply | Quote

Replies

I would copy the items to an array, sort the array, and repopulate the combo box.

Reply | Quote

That’s what I thought. Thanks for confirming.

Reply | Quote

Is there a native VBA way to sort arrays yet?

Due to problems with this code in Office 2000, we checked the version before sorting:

If Application.Version = “8.0” Then ‘Word97 only
WordBasic.SortArray (myArray())
End If

Reply | Quote

Not a native way.

What problems were there in the Word2000 Wordbasic.SortArray?

Have you chec ked out Charlotte’s example for a way to sort? (You no doubt have your own anyway)

Reply | Quote

I think the WordBasic.SortArray method wasn’t supported, at least in the templates I was creating. Not sure why.

Actually, I don’t have my own sort routine; somehow, the stuff miraculously turned out to be sorted when it displayed in the ListBox, so I never had to write one.

Thanks for the link, though, you never know…

Reply | Quote

Jeff,

I hate “miraculously”. Sounds like something wrong.

Was the listbox bound to a datasource- and hence sorted that way?

Reply | Quote

If the items were by chance, members of a collection, there seems to be some built-in sorting.
Here’s an example from something I’m working on right at the moment:

If you go into a document and insert bookmarks around the first three paragraphs like so:

Bookmark2 goes here
Bookmark3 goes here
Bookmark1 goes here

And then run code like this:

With lstBmkNames
For n = 1 To objBmks.Count
.AddItem objBmks(n)
Next n
End With

What will display in the ListBox will be:

Bookmark1
Bookmark2
Bookmark3

So it seems that collections internally sort themselves by name, regardless of the order in which the items actually occur in the document.
(You might have already known this one, but it was new to me! )

Reply | Quote

Okay, there apparently wasn’t a miracle. I was sorting my array of folder names, because of this advice in the help under Dir:

Tip Because file names are retrieved in no particular order, you may want to store returned file names in an array, and then sort the array.

In fact, though, my array turned out to be nicely alphabetical without me doing any work. I think it is the way Win98 interacts with NT4 server, since I get the same results at a DOS prompt. Just dumb luck!

Reply | Quote

Ah, that reminds me! Why FileSearch is better than Dir:

(From VBA help:)

FileSearch object: Begins the search for the specified files.

Syntax 1

expression.Execute(SortBy, SortOrder, AlwaysAccurate)

expression Required. An expression that returns a FileSearch object.

SortBy Optional Variant. The method used to sort the returned files. Can be one of the following MsoSortBy constants: msoSortbyFileName, msoSortbyFileType, msoSortbyLastModified, or msoSortbySize.

SortOrder Optional Variant. The order in which the returned files are sorted. Can be either of the following MsoSortOrder constants: msoSortOrderAscending or msoSortOrderDescending.

AlwaysAccurate Optional Boolean. True to have the file search include files that have been added, modified, or deleted since the file index was last updated. The default value is True.

===========================
An optional SortBy method!!
And as an added bonus, the .FoundFiles object is an array.

Reply | Quote

 Public Function SortListBox(lb As ListBox, intCol As Integer)
    If lb.ListCount > 0 Then
        Dim strar() As String
        ReDim strar(lb.ListCount - 1, lb.ColumnCount - 1)
        Dim lngRow As Long
        Dim intColumn As Integer
        For lngRow = 0 To lb.ListCount - 1
            For intColumn = 0 To lb.ColumnCount - 1
                strar(lngRow, intColumn) = lb.List(lngRow, intColumn)
            Next intColumn
        Next lngRow
        Call QCSort(strar, 0, UBound(strar, 1), False, intCol)
        For lngRow = 0 To lb.ListCount - 1
            For intColumn = 0 To lb.ColumnCount - 1
                lb.List(lngRow, intColumn) = strar(lngRow, intColumn)
            Next intColumn
        Next lngRow
    Else
    End If
End Function

Public Function QCSort(strList() As String, ByVal lLbound As Long,_
 ByVal lUbound As Long, ByVal boolCaseSensitive As Boolean, _
intColumn As Integer)
' March 3rd Chris Greaves Implemented a Column key (hence qCsort)
    Dim strTemp As String
    Dim strBuffer() As String
    Dim lngCurLow As Long
    Dim lngCurHigh As Long
    Dim lngCurMidpoint As Long
    lngCurLow = lLbound ' Start current low and high at actual low/high
    lngCurHigh = lUbound
    If lUbound <= lLbound Then Exit Function ' Error!
    lngCurMidpoint = (lLbound + lUbound)  2 ' Find the approx midpoint of the array
' Pick as a starting point (we are making
' an assumption that the data *might* be in semi-sorted order already!
    strTemp = MyUCase(strList(lngCurMidpoint, intColumn), boolCaseSensitive) 
    Do While (lngCurLow <= lngCurHigh)
        Do While MyUCase(strList(lngCurLow, intColumn), boolCaseSensitive) < strTemp
            lngCurLow = lngCurLow + 1
            If lngCurLow = lUbound Then Exit Do
        Loop
        Do While strTemp < MyUCase(strList(lngCurHigh, intColumn), boolCaseSensitive)
            lngCurHigh = lngCurHigh - 1
            If lngCurHigh = lLbound Then Exit Do
        Loop
        If (lngCurLow <= lngCurHigh) Then ' if low is <= high then swap
            Call SwapRows(strList, lngCurLow, lngCurHigh)
            lngCurLow = lngCurLow + 1 ' CurLow++
            lngCurHigh = lngCurHigh - 1 ' CurLow--
        End If
    Loop
    If lLbound < lngCurHigh Then ' Recurse if necessary
        QCSort strList(), lLbound, lngCurHigh, boolCaseSensitive, intColumn
    End If
    If lngCurLow < lUbound Then ' Recurse if necessary
        QCSort strList(), lngCurLow, lUbound, boolCaseSensitive, intColumn
    End If
'Sub TESTQCSort()
'Dim strar(6, 3) As String
'strar(0, 0) = "january"
'strar(0, 1) = "february"
'strar(0, 2) = "march"
'strar(0, 3) = "april"
'strar(1, 0) = "may"
'strar(1, 1) = "june"
'strar(1, 2) = "july"
'strar(1, 3) = "august"
'strar(2, 0) = "september"
'strar(2, 1) = "october"
'strar(2, 2) = "november"
'strar(2, 3) = "december"
'strar(3, 0) = "Monday"
'strar(3, 1) = "Tuesday"
'strar(3, 2) = "Wednesday"
'strar(3, 3) = "Thursday"
'strar(4, 0) = "Friday"
'strar(4, 1) = "Saturday"
'strar(4, 2) = "Sunday"
'strar(4, 3) = "january"
'strar(5, 0) = "february"
'strar(5, 1) = "march"
'strar(5, 2) = "april"
'strar(5, 3) = "may"
'strar(6, 0) = "june"
'strar(6, 1) = "july"
'strar(6, 2) = "august"
'strar(6, 3) = "september"
'Selection.TypeParagraph
'Debug.Print " "
'Call DumpArray(strar)
'Call QCSort(strar, LBound(strar, 1), UBound(strar, 1), False, 2)
'Selection.TypeParagraph
'Debug.Print " "
'Call DumpArray(strar)
'End Sub
End Function

Reply | Quote

Cool dude, but where’s the SwapRows function? Thanks though for that time saver. I tell you what. I’ll play around with your code and replace your SwapRows with a swap routine that uses the Windows API CopyMemory function:

Private Declare Sub CopyMemory Lib “kernel32” Alias “RtlMoveMemory” (pDst As Any, pSrc As Any, ByVal ByteLen As Long)

…if I have time.

Reply | Quote

 Public Function SwapRows(strList() As String, lngCurLow As Long, lngCurHigh As Long)
    Dim lngCols As Long
    lngCols = UBound(strList, 2)
    Dim strBuffer As String
    Dim lngC As Long
    For lngC = 0 To lngCols
        strBuffer = strList(lngCurLow, lngC)
        strList(lngCurLow, lngC) = strList(lngCurHigh, lngC)
        strList(lngCurHigh, lngC) = strBuffer
    Next lngC
End Function

Reply | Quote

That’s better. Thanks.

Reply | Quote

You’re welcome. I’m not such a bad sort after all, eh?

You mentioned a (presumably) faster way to sort arrays, In-memory? I’d be interested in a simple example that I could then clone and pass off as my own (grin!). I do a lot of sorting of arrays, listboxes and internal arrays (as, for example, when I am dropping duplicate paragraphs of a document)

Perhaps we could be nice to the lounge and collaborate on a routine by private email and then publish it? Is that allowed?

Reply | Quote

BTW, your pic is busted.

Sure. We can to that. You want to get a head start? Check out that API. I’ve got a bit more research to do. But it sounds like you can use that API function to swap string pointers instead of moving string values to memory locations. Should be a lot faster.

Reply | Quote

>BTW, your pic is busted.

No it’s not. I attach the original ….

Reply | Quote

Well, Ok. I’ve attached the gif I see when you post. Doesn’t look a thing like you.

Reply | Quote

Split personality? Not what I’m cracked up to be?

Anyway, noone else is posting their REAL photos, are they? I’m just being honest about it …..

Another icon shattered …..

Reply | Quote

Ok. You don’t think I’m 8 years old do you?

Reply | Quote

Well, … since you ASK, ……

(hah hah hah!)

Reply | Quote