MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

How do I sort an array?

  Asked By: Steven    Date: Jan 22    Category: MS Office    Views: 1441

My BASIC knowledge goes back to the early
1980s. I am just getting to grips with ExcelVBA.

Is there a function within VBA to sort arrays? I have been dumping the
array to the spreadsheet and then using the data sort function, but
that's a bit crude.



2 Answers Found

Answer #1    Answered By: Perdita Lopez     Answered On: Jan 22

Here is a VBA QuickSort routine I picked up somewhere years ago. It has worked
well for me.

Sub QuickSort(SortMe() As String, lowbound As Long, hibound As Long)
'Recursive QuickSort routine for VBA. Sorts an array  of strings into ascending
'SortMe() is the array of strings to be sorted. lowbound is the index of the
'element in the array (usually 0 or 1). hibound is the index of the last
element in
'the array.
Dim low As Long, high As Long, midval As String, Temp As String
low& = lowbound&
high& = hibound&
midval$ = SortMe((low + high) / 2)
While (low <= high)
While (SortMe(low) < midval And low < hibound)
low = low + 1
While (midval < SortMe(high) And high > lowbound)
high = high - 1
If (low <= high) Then
Temp = SortMe(low)
SortMe(low) = SortMe(high)
SortMe(high) = Temp
low = low + 1
high = high - 1
End If
If (lowbound < high) Then
Call QuickSort(SortMe(), lowbound, high)
End If
If (low < hibound) Then
Call QuickSort(SortMe(), low, hibound)
End If
End Sub

Answer #2    Answered By: Faith Hughes     Answered On: Jan 22

I wonder why Microsoft doesn't
include Sort within VBA? It's such a common task.

Didn't find what you were looking for? Find more on How do I sort an array? Or get search suggestion and latest updates.