- Code: Select all
Option Base 0
Private Sub SortNumericArray(ByRef A As Variant)
' sort numbers in ascending order
Dim Tmp As String
Dim Done As Boolean
Dim i As Long
Do
Done = True
For i = 1 To UBound(A)
If Val(A(i)) < Val(A(i - 1)) Then
Tmp = CStr(A(i))
A(i) = A(i - 1)
A(i - 1) = Tmp
Done = False
End If
Next i
Loop Until Done
End Sub
- Code: Select all
If Val(A(i)) < Val(A(i - 1)) Then
To call the procedure you need to provide a variant of array type such as you could generate with this code:
- Code: Select all
Dim MyArray As Variant
MyArray = Array(5,55,3,13,99,17,5,3.314)
If you need to read your numbers from a worksheet code like this will do the job:
- Code: Select all
Dim MyArray() As Variant
Dim R As Long
Dim i As Long
For R = FirstRow To LastRow
ReDim MyArray(i)
MyArray(i) = Cells(R, “A”).Value
i = i + 1
Next R
It is important that your array starts with a lower bound of 0. Therefore you should not omit the Option Base 0 statement at the top of your module.
Use this kind of code to call the procedure:
- Code: Select all
SortNumericArray MyArray
The array is passed to the sub ByRef, meaning that only the address is provided, rather than the array itself. That saves time by not transferring potentially large amount of data. It also means that the original sequence of the MyArray elements is irretrievably lost during the sort. The only way to get it back would be to read it again from the source. If, for some reason, you need the original, unsorted version, send a copy thereof for sorting. In the above procedure call MyArray is itself different before and after.
The procedure here offered was designed for sorting indices. It will do lists from a few dozen to a few hundred items efficiently. Yet, it can’t match Excel’s own sorting capabilities. If you have thousands of items to sort you should consider faster alternatives.