How can I sort an array() ?

Posted by Bruno on December 03, 2001 2:14 AM

Hello,
How can I sort the array maps() in the following example ?

kinds regards,
Bruno
==============

Sub SortMaps()

Dim maps(100) As Variant
' Display the names in C:\ that represent directories.
MyPath = "c:\" ' Set the path.
MyName = Dir(MyPath, vbDirectory) ' Retrieve the first entry.
counter = 1

Do While MyName &LT;&GT; ""
If MyName &LT;&GT; "." And MyName &LT;&GT; ".." Then
If (GetAttr(MyPath & MyName) And vbDirectory) = vbDirectory Then
maps(counter) = MyName
counter = counter + 1
End If
End If
MyName = Dir 'next entry
Loop

'result
For dummy = 1 To counter - 1
MsgBox maps(dummy), vbOKOnly, "One by one...(not sorted)"
Next

End Sub

Posted by Dank on December 03, 2001 4:15 AM

Here is the one way. It uses Excel's built in sorting functionality and is pretty quick.

Public Sub Sort(ByRef Arr As Variant, Ascending As Boolean)
Dim rnge As Range, sht As Worksheet, SortOrder As Integer

If Ascending Then SortOrder = 1 Else SortOrder = 2

Set sht = Worksheets.Add
Set rnge = sht.Range(Cells(1, 1), Cells(UBound(Arr) + 1, 1))
rnge = Arr
rnge.Sort rnge.Cells(1, 1), SortOrder
Arr = rnge
Application.DisplayAlerts = False
sht.Delete
Application.DisplayAlerts = True
End Sub

To use this try something like this:-

Sub testofSort()
'Sort an array of 10000 items
Dim MyArray

'The activesheet has 10000 random numbers in column A
MyArray = ActiveSheet.UsedRange.Value

Sort MyArray, True
ActiveSheet.UsedRange.Value = MyArray

End Sub

You could sort the array without having to add a sheet but this method works well (and is very quick 50000 items in under a second on my PC).

Regards,
Daniel.

Posted by Bruno on December 03, 2001 11:11 PM

Thanks Daniel

Thanks Daniel for your answer
Bruno