Sorting in a macro

[Don Wiss]

I have xl2002. In in VBA. I have a vector (or one column matrix) of short
strings. I'd like to sort them. Is the easiest way to place them on a sheet
someplace, run sort, and then read them back in?

Don <donwiss at panix.com>.

 

[Fredrik Wahlgren]

I have xl2002. In in VBA. I have a vector (or one column matrix) of short
strings. I'd like to sort them. Is the easiest way to place them on a sheet
someplace, run sort, and then read them back in?

Don <donwiss at panix.com>.

Use Data|Sort...

 

[Tom Ogilvy]

This example builds an array of filenames and then sorts them.

Sub UpdateFileLB()
Dim Fname As String, FileArray() As String
Dim FCounter As Long
Fname = Dir("C:\excel\files\*.xls")
Do While Fname <> ""
FCounter = FCounter + 1
ReDim Preserve FileArray(1 To FCounter)
FileArray(FCounter) = Fname
Fname = Dir()
Loop
QuickSort FileArray, 1, FCounter
End Sub

Sub QuickSort(SortArray, L As Long, R As Long)
' L is the lower bound of the array
' R is the upper bound of the array
Dim I As Long, J As Long, x As Variant, Y As Variant
I = L
J = R
x = SortArray((L + R) / 2)
While (I <= J)
While (SortArray(I) < x And I < R)
I = I + 1
Wend
While (x < SortArray(J) And J > L)
J = J - 1
Wend
If (I <= J) Then
Y = SortArray(I)
SortArray(I) = SortArray(J)
SortArray(J) = Y
I = I + 1
J = J - 1
End If
Wend
If (L < J) Then Call QuickSort(SortArray, L, J)
If (I < R) Then Call QuickSort(SortArray, I, R)
End Sub

 

[Don Wiss]

Use Data|Sort...

Uh, what does this have to do with a macro? I know how to use a macro to
sort on a sheet. I asked if that was the only way, as the strings are in a
macro and will end up only in the macro.

Don <donwiss at panix.com>.

 

[Don Wiss]

This example builds an array of filenames and then sorts them.

Sub QuickSort(SortArray, L As Long, R As Long)

[snipped..]

Thanks. Works beautifully. Now after this I will remove duplicates.
Possibly something that could (optionally) be inside QuickSort.

Don <donwiss at panix.com>.

 

[Dave Peterson]

You may want to look at the way John Walkenbach did it:
http://j-walk.com/ss/excel/tips/tip47.htm

This example builds an array of filenames and then sorts them.

Sub QuickSort(SortArray, L As Long, R As Long)

[snipped..]

Thanks. Works beautifully. Now after this I will remove duplicates.
Possibly something that could (optionally) be inside QuickSort.

Don <donwiss at panix.com>.

 

[Don Wiss]

You may want to look at the way John Walkenbach did it:
http://j-walk.com/ss/excel/tips/tip47.htm

Interesting. I have never used a collection. Nor been aware of them. Though
now in sorted order removing duplicates is trivial, and I do need it to be
sorted for the next step.

Don <donwiss at panix.com>.

 

[Dave Peterson]

John had a sort in that sample code, too.

Interesting. I have never used a collection. Nor been aware of them. Though
now in sorted order removing duplicates is trivial, and I do need it to be
sorted for the next step.

Don <donwiss at panix.com>.

 

[Tom Ogilvy]

A bubble sort - it could be replaced with a quicksort. Some testing I did a
long time ago, it appeared to be faster to use the collection to get
uniques, then do the sort on a reduced list vice sort and array and then
have your code (not using a collection) go through and eliminate the
duplicates. The speed difference was more noticeable as the size of the
list increased (the collection approach stayed fairly constant as I recall).

 

[Don Wiss]

A bubble sort - it could be replaced with a quicksort. Some testing I did a
long time ago, it appeared to be faster to use the collection to get
uniques, then do the sort on a reduced list vice sort and array and then
have your code (not using a collection) go through and eliminate the
duplicates. The speed difference was more noticeable as the size of the
list increased (the collection approach stayed fairly constant as I recall).

Hi Tom,

I finally got around to finishing the macro that used this. I ended up
using the collection method. As neat as your sort macro was, the problem
was all around simpler when using a Collection.

The list will never be more than a few hundred in this use, but duplicates
will be many. So removing them first made sense.

Thanks, Don <donwiss at panix.com>.