VBA Sort

[Brad]

Does VBA have a built-in sort function? Seems like it should, but I can't
find one. If the answer is YES, please give syntax and an example.

I saw that there is a DOCMD.Runcommand acSortAscending or something like
that, but it looked like it could only be used with a control such as list
box. I couldn't find the syntax or an example that helped. I just want to
sort an array of strings. Thanks for any help.

Brad

 

[Howard Kaikow]

VBA does not have a Sort.

 

[Tomm]

Here's an efficient function:

Sub Quicksort(aSortArray, l As Long, Num_Rows As Long, Sort_Column As Long)
Dim i As Long, c As Long, j As Long, x As String, y As String
i = l
j = Num_Rows
x = aSortArray(((l + Num_Rows) / 2), Sort_Column)
While (i <= j)
While (aSortArray(i, Sort_Column) < x And i < Num_Rows)
i = i + 1
Wend
While (x < aSortArray(j, Sort_Column) And j > l)
j = j - 1
Wend
If (i <= j) Then
For c = LBound(aSortArray, 2) To UBound(aSortArray, 2)
y = aSortArray(i, c)
aSortArray(i, c) = aSortArray(j, c)
aSortArray(j, c) = y
Next c
i = i + 1
j = j - 1
End If
Wend
If (l < j) Then Call Quicksort(aSortArray, l, j, Sort_Column)
If (i < Num_Rows) Then Call Quicksort(aSortArray, i, Num_Rows,
Sort_Column)
End Sub


And sample code how it's used

' Sort the items in the ListBox.
Sub SortListBox(list_box As ListBox)

Dim values() As String
Dim num_items As Long
Dim i As Integer

' Put the list choices in a string array.
num_items = list_box.ListCount

If num_items = 0 Then Exit Sub

ReDim values(1 To num_items, 1 To 3)

For i = 1 To num_items
values(i, 1) = list_box.List(i - 1, 0)
values(i, 2) = list_box.List(i - 1, 1)
values(i, 3) = IIf(IsNull(list_box.List(i - 1, 2)), 0,
list_box.List(i - 1, 2))
Next i

' Sort the list.
Quicksort values, 1, num_items, 1

' Put the items back in the ListBox.
list_box.Clear
For i = 1 To num_items
list_box.AddItem values(i, 1)
list_box.List(list_box.ListCount - 1, 1) = values(i, 2)
list_box.List(list_box.ListCount - 1, 2) = values(i, 3)

Next i

End Sub

 

[Howard Kaikow]

FYI.

Up until a couple of years ago, for Word VBA, I used to recommend that
folkes use the WordBasic SortArray in VBA, as, surely, one could not write
VBA code to do better than the built-in sort in WordBasic.

A few years ago, I decided to test my own advice and found that was I ever
wrong!
The built-in WordBasic sort is awful.

I then started investigating various implementations of QuickSort, each of
which beat the WordBasic Sort by a huge amount.

However, I found "significant" variations in performance of various
QuickSort algorithms published in various VBA books for Access, Excel and
Word. I was able, without much difficulty, to write my own QuickSort which
beat all the others.

I then decided to compare my results, so I made a Sort Demo project to test
things.
I was going to release the Sort Demo, but I could not legally include the
code of others from those books, not to mention the pissing contest that
would ensue even if I could post the code.

So I narrowed my demo to just compare code that I wrote, for a few sorting
methods, with WordBasic.

Perhaps, this thread will finally prompt me to release the demo

 

[Howard Kaikow]

Here's some info.

Used random integer data.
Data was not, intentionally, partially sorted.
10000 numbers were generated for each run.
Numbers were stored in a vector, not a multiple dimension array.
Numbers were sorted as strings.

4 runs were made.
The following are the average times, in milliseconds, for each algorithm.

Bubble sort: 49516
Counting sort: 20
WordBasic SortArray: 40423.25
Quick sort: 247.5

I coded the bubble, Counting and Quick sort codes.