[VBA] Implementing the Quick Sort Algorithm: How to Sort Arrays Fast

Introduction

When sorting arrays containing a large amount of data in VBA, processing speed becomes a critical factor. Among the many sorting algorithms available, Quick Sort is known as one of the fastest, as its name suggests.

Quick Sort is based on the concept of “Divide and Conquer.” It works by selecting a reference value (pivot), dividing the data into two groups (smaller and larger than the pivot), and then recursively dividing those groups. This process eventually sorts the entire dataset.

In this article, I will explain the concept of Quick Sort and how to implement it efficiently in VBA using an “in-place” method, which sorts the data without creating unnecessary extra arrays.

The Quick Sort Algorithm

Quick Sort operates mainly in the following three steps:

1. Pivot Selection: Select one element from the array to serve as the reference. This is called the “pivot” (commonly the middle value is chosen).
2. Partitioning: Move all values smaller than the pivot to its left and all values larger than the pivot to its right. Once this operation is done, the pivot is fixed in its final sorted position.
3. Recursion: Apply the Quick Sort logic (steps 1–3) again to the group on the left of the pivot and the group on the right.

By repeating this recursive process until each group contains only one element, the entire array is neatly sorted.

VBA Implementation Code

The following code generates an array with random numbers, sorts it in ascending order using Quick Sort, and writes the results to a worksheet.

Complete Code

' Quick Sort Execution Sample
Sub RunQuickSortExample()
    Dim dataArray() As Variant
    Dim arraySize As Long, i As Long
    
    arraySize = 20 ' Number of data items to generate
    ReDim dataArray(1 To arraySize)
    
    '--- 1. Generate random test data and write to Column A ---
    For i = 1 To arraySize
        Randomize
        dataArray(i) = Int(1000 * Rnd + 1)
        Worksheets("Sheet1").Cells(i, "A").Value = dataArray(i)
    Next i
    
    '--- 2. Execute Quick Sort ---
    ' Start sorting the entire array (from the first to the last index)
    ExecuteQuickSort dataArray, LBound(dataArray), UBound(dataArray)
    
    '--- 3. Write sorted results to Column B ---
    For i = 1 To arraySize
        Worksheets("Sheet1").Cells(i, "B").Value = dataArray(i)
    Next i
    
    MsgBox "Sorted values in Column A and wrote them to Column B."
End Sub


' Procedure to execute Quick Sort recursively
Private Sub ExecuteQuickSort(ByRef arr As Variant, ByVal leftIdx As Long, ByVal rightIdx As Long)
    Dim pivot As Variant
    Dim temp As Variant
    Dim i As Long, j As Long
    
    ' Process only if left index is smaller than right index
    If leftIdx < rightIdx Then
        ' Select the middle value as the pivot
        pivot = arr((leftIdx + rightIdx) \ 2)
        i = leftIdx
        j = rightIdx
        
        '--- Partitioning ---
        Do
            ' Find value greater than or equal to pivot from the left
            Do While arr(i) < pivot
                i = i + 1
            Loop
            ' Find value less than or equal to pivot from the right
            Do While arr(j) > pivot
                j = j - 1
            Loop
            
            If i >= j Then Exit Do
            
            ' Swap values
            temp = arr(i)
            arr(i) = arr(j)
            arr(j) = temp
            
            i = i + 1
            j = j - 1
        Loop
        
        '--- Recursive Calls ---
        ExecuteQuickSort arr, leftIdx, j  ' Sort the left side
        ExecuteQuickSort arr, i, rightIdx ' Sort the right side
    End If
End Sub

Code Explanation

ExecuteQuickSort Procedure

This Sub receives the array itself by reference (ByRef arr As Variant). This means that when elements are swapped inside the procedure, the original dataArray in the calling macro is directly modified (in-place sort).

• leftIdx, rightIdx: These indicate the section of the array currently being sorted (start index and end index).

Partitioning Loop

The code inside Do ... Loop is the heart of Quick Sort.

1. Do While arr(i) < pivot: Moves the pointer i from left to right to find a value greater than or equal to the pivot.
2. Do While arr(j) > pivot: Moves the pointer j from right to left to find a value less than or equal to the pivot.
3. Swapping: When i and j find such values, the two elements are swapped.

By repeating this until i and j cross, two groups (smaller and larger) are created based on the pivot.

Recursive Calls

Once partitioning is finished, the procedure calls itself again:

• ExecuteQuickSort arr, leftIdx, j sorts the sub-array to the left of the split.
• ExecuteQuickSort arr, i, rightIdx sorts the sub-array to the right.

This recursively sorts each divided group.

Summary

In this article, I explained how to implement Quick Sort, a very fast sorting algorithm, in VBA.

• Quick Sort is based on the “Divide and Conquer” method and is generally very fast.
• It splits data into two groups based on a pivot and repeats the process recursively.
• By passing the array indices as arguments, you can sort in-place efficiently.

Although the logic is more complex than simple sorts like Bubble Sort, its performance is outstanding. It is one of the best choices for optimizing macros that handle large amounts of array data.