MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

sorting an array in VBA without writing it in excel sheet

  Asked By: Dora    Date: Feb 05    Category: MS Office    Views: 9681

I needed some help regarding sorting an array in vba.
If we have data in excel sheet, it can be sorted via sort methods
either by calling from VBA or in excel itself.

can we directly sort an array in vba with some standard vba sorting
subroutine without first writing it in excel sheet? If yes, do you
know name of the subroutine.



1 Answer Found

Answer #1    Answered By: Geb Chalthoum     Answered On: Feb 05

Here's a recursive Quicksort VBA subroutine  I use:

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

You will need to copy & paste the above code into a code module to use it.
Hope this helps,

Didn't find what you were looking for? Find more on sorting an array in VBA without writing it in excel sheet Or get search suggestion and latest updates.