Sorting in a macro
 

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.

Sorting in a macro
 

"Don Wiss" wrote in message
...
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...

Sorting in a macro
 

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

--
Regards,
Tom Ogilvy


"Don Wiss" wrote in message
...
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.

Sorting in a macro
 

Fredrik Wahlgren wrote:

Don Wiss wrote:
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?

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.

Sorting in a macro
 

On Sun, 9 Jan 2005 19:41:53 -0500, "Tom Ogilvy" wrote:

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.

Sorting in a macro
 

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



Don Wiss wrote:

On Sun, 9 Jan 2005 19:41:53 -0500, "Tom Ogilvy" wrote:

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

Sorting in a macro
 

On Sun, 09 Jan 2005, Dave Peterson wrote:

Don Wiss wrote:
Now after this I will remove duplicates.
Possibly something that could (optionally) be inside QuickSort.

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.

Sorting in a macro
 

John had a sort in that sample code, too.

Don Wiss wrote:

On Sun, 09 Jan 2005, Dave Peterson wrote:

Don Wiss wrote:
Now after this I will remove duplicates.
Possibly something that could (optionally) be inside QuickSort.

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

Sorting in a macro
 

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).

--
Regards,
Tom Ogilvy


"Dave Peterson" wrote in message
...
John had a sort in that sample code, too.

Don Wiss wrote:

On Sun, 09 Jan 2005, Dave Peterson wrote:

Don Wiss wrote:
Now after this I will remove duplicates.
Possibly something that could (optionally) be inside QuickSort.

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

Sorting in a macro
 

On Mon, 10 Jan 2005 22:57:58 -0500, Tom Ogilvy wrote:

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.