Sorting single dimension arrays in Excel 2003 VBA 1

[phudgens]

I have done some searching in VBA help and online for a way to sort single dimension arrays of string or date data and can't find anything. The only specific reference I found was that VBA can't do it. I'm hoping to find a way to sort arrays of string and/or date data in Excel 2003 VBA without having to write it out to a worksheet. Does anyone know of a way?

 

[PHV]

A starting point (bubble sort):

Sub tstSort()
Dim variable1(1 To 100) As Single, N As Integer, M As Integer, tmp As Single
For N = 1 To 100
    variable1(N) = Rnd(1000)
Next
For N = 1 To 99
  For M = N To 100
    If variable1(N) > variable1(M) Then
      tmp = variable1(M)
      variable1(M) = variable1(N)
      variable1(N) = tmp
    End If
  Next
Next
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Gruuuu]

I would strongly suggest looking at Insertion Sort IF you must avoid putting it on a spreadsheet.

However...

Remember that Excel is not written in VBA. It is almost certainly written in C++. Any sorting algorithms in Excel are probably leaps and bounds more efficient than a homebrew algo.
The efficiency lost in writing/reading from the sheet will be made up for, and you will definitely save yourself some headache.
If you don't want to see what's going on, just turn off ScreenUpdating and Calculations, which of course will speed things up even more.

 

[mscallisto]

Here's another method:

http://www.cpearson.com/excel/SortingArrays.aspx

In addition, in the macro you can right click on Arr and choose "add Watch"

In the watch window click the + sign to see all the array elements

To monitor the sort progress go to the first line of code and click F8 for each line of code and watch what happens to your array in the Watch window.

sam

 

[Gruuuu]

QSort is a good option if you can't use a spreadsheet. I use it when I'm not working in Excel. Good call mscallisto!

 

[mscallisto]

Mille Grazie Gruuuu

 

[phudgens]

Thanks to everyone for your input. PHV's method works slick - including handling duplicate strings in the input array. I'm not handling immense amounts of data so I'm not very concerned at this point about efficiency. I just needed a quick way to sort an array without having to write all the code to output the data to the sheet, sort it, and re-gather the data. Thanks very much.