VBA sort array

To sort an array, there are a number of algorithms (e.g. selection sort, insertion sort, bubble sort, quick sort,...).
In this example, a simple selection sort algorithm is used to sort an array of random values.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim values(100) As Variant
Dim sortedValues As Variant
Dim i As Integer
For i = 0 To UBound(values)
   Randomize 'initialize random seed based on system time
   values(i) = Int(100 * Rnd + 1) 'store random values in array
Next

printArray values 'Output the array
sortArray values 'sort array
printArray values 'Output the sorted array
End Sub

Sub sortArray(arr() As Variant)
   'Selection sort algorithm
   Dim i, j, min, temp As Integer
   For i = 0 To UBound(arr)
     min = i
     For j = i + 1 To UBound(arr)
        If arr(j) < arr(min) Then
           min = j
        End If
     Next
     temp = arr(min)
     arr(min) = arr(i)
     arr(i) = temp
          
   Next
  
End Sub

Sub printArray(arr() As Variant, Optional str As String = "")
   For Each Item In arr
      str = str & Item & vbTab
   Next
   MsgBox str
End Sub

Posted by: Dara | post date: 02-28-2013 | Subject: VBA for MS Excel




This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.