Creating small "recordset" or sorting a numeric 1-dim array

  • Thread starter Thread starter Anthony Cravero
  • Start date Start date
A

Anthony Cravero

Hi Everyone,

I am attempting to sort a small one-dimensional array of integers (about 10
or 15 total) that are currently in no particular order into a
greatest-->least. Is there a way that I can do this in VBA? Or, is it
possible to create a recordset in Excel from my array, which I can then
sort? The array is variable, and will often change, so the recordset (and
the array) would be a temporary solution, created & deleted each time the
macro runs.

Thanks,
Anthony
 
Anthony,

See the code below for a bubble sort function that will return a
sorted array. The TryIt macro shows how to use it in code.

HTH,
Bernie
MS Excel MVP

Sub TryIt()
Dim myArray(1 To 2) As Integer
Dim TempArray As Variant
myArray(1) = 10
myArray(2) = 1
MsgBox myArray(1)
MsgBox myArray(2)
TempArray = ArrayBubbleSort(myArray,TRUE)
MsgBox TempArray(1)
MsgBox TempArray(2)
End Sub

Function ArrayBubbleSort( _
myArray As Variant, _
Optional Ascending As Boolean) _
As Variant
Dim myTemp As Variant
Dim myInt() As Variant
Dim i As Integer
Dim j As Integer

ReDim myInt(LBound(myArray) To UBound(myArray))
For i = LBound(myArray) To UBound(myArray)
myInt(i) = Val(Trim(myArray(i)))
Next i
'Do the sort
For i = LBound(myInt) To UBound(myInt) - 1
For j = i + 1 To UBound(myInt)
If Ascending Then
If myInt(i) > myInt(j) Then
myTemp = myInt(j)
myInt(j) = myInt(i)
myInt(i) = myTemp
End If
Else
If myInt(i) < myInt(j) Then
myTemp = myInt(j)
myInt(j) = myInt(i)
myInt(i) = myTemp
End If
End If
Next j
Next i

'Return the array
ArrayBubbleSort = myInt
End Function
 
Back
Top