Quick Sort in VBA: Built-In Function or Custom Code?

No: VBA does not include a built-in procedure or keyword named QuickSort or quick sort. Call is a real VBA statement, but it can call only a procedure that exists in your project or a referenced library, so you must write or import a sorting routine—or use an Excel sorting feature instead.

What “Call quick sort” means in VBA

VBA separates the instruction to call a procedure from the procedure’s name. The keyword Call asks VBA to run a Sub procedure; it does not provide the procedure itself. If your code says Call QuickSort but your project contains no accessible Sub named QuickSort, VBA reports an error such as “Sub or Function not defined.” A procedure named quick sort cannot fix that: VBA names do not contain spaces.

VBA is not case-sensitive, so QuickSort and quicksort refer to the same spelling of an identifier. But the spelling still has to match a procedure that is available to the calling code. That procedure might be in the same module, another module in the workbook, or a referenced library. A Quick Sort example found online is custom code, not a built-in feature that becomes available just by typing its name.

What the Call statement does

For a Sub procedure with arguments, VBA permits two common calling styles. With Call, put the argument list in parentheses: Call QuickSortLong(numbers, first, last). Without Call, omit those parentheses: QuickSortLong numbers, first, last. The parentheses rule is a frequent source of confusing syntax errors. A Function returns a value and is usually used in an expression; the example below is a Sub that changes the array it receives.

The name in the call is just a name. You could define a procedure called SortNumbers, QuickSortLong, or another legal identifier. It is the body of that procedure that implements the algorithm. Naming a Sub QuickSort does not make it a Microsoft-supplied sort routine, and it does not make that routine available in other projects unless you copy it or otherwise provide it there.

How Quick Sort works

Quick Sort is a comparison-based sorting algorithm. It chooses a value called a pivot, rearranges the current section so smaller values are on one side and larger values on the other, then repeats that partitioning on the resulting sections. It stops when a section has fewer than two elements. The code does not need to create a second full-size array, which can make this approach useful when sorting an array in place.

The sample uses the value at the middle index as its pivot. Two indexes move inward: one looks for a value that belongs on the right, and the other looks for a value that belongs on the left. When they find a pair on the wrong sides, the procedure swaps them. Once the indexes cross, it recursively sorts the remaining partitions. Equal values are handled by the crossing and swapping logic; a list containing duplicates should not cause an endless loop.

Quick Sort usually takes roughly O(n log n) comparisons, but its worst case is O(n²), depending on the pivot choices and input. This implementation chooses a middle-position element, a reasonable simple strategy, not a guarantee against every worst-case arrangement. Quick Sort is also generally not stable: equal items are not guaranteed to keep their original relative order. That distinction matters when sorting records by one field while expecting a prior order to be preserved.

A Quick Sort procedure for Long arrays

This example sorts a one-dimensional array of VBA Long integers in ascending order. Paste it into a standard module in the Visual Basic Editor. It is deliberately limited to Long arrays; it is not a universal routine for text, dates, objects, or mixed Variant values.

Option Explicit

Public Sub QuickSortLong(ByRef values() As Long, _
                         ByVal first As Long, _
                         ByVal last As Long)
    Dim low As Long
    Dim high As Long
    Dim pivot As Long
    Dim temp As Long

    low = first
    high = last
    pivot = values(first + (last - first) \ 2)

    Do While low <= high
        Do While values(low) < pivot
            low = low + 1
        Loop
        Do While values(high) > pivot
            high = high - 1
        Loop

        If low <= high Then
            temp = values(low)
            values(low) = values(high)
            values(high) = temp
            low = low + 1
            high = high - 1
        End If
    Loop

    If first < high Then QuickSortLong values, first, high
    If low < last Then QuickSortLong values, low, last
End Sub

The backslash in the pivot expression is VBA’s integer-division operator. Computing the midpoint as first + (last - first) \ 2 avoids adding the two indexes before dividing. The procedure assumes that first and last describe a valid, non-empty section of the array. The recursive calls pass smaller sections until no further sorting is needed.

How to call it and check the result

To try the routine, put this separate test Sub in the same standard module. It uses a fixed-size Long array, passes its actual lower and upper bounds, and prints the sorted values to the Immediate window.

Public Sub TestQuickSortLong()
    Dim numbers(0 To 5) As Long
    Dim i As Long

    numbers(0) = 7
    numbers(1) = 2
    numbers(2) = 9
    numbers(3) = 2
    numbers(4) = -1
    numbers(5) = 4

    Call QuickSortLong(numbers, LBound(numbers), UBound(numbers))

    For i = LBound(numbers) To UBound(numbers)
        Debug.Print numbers(i)
    Next i
End Sub

The printed sequence is -1, 2, 2, 4, 7, 9. In Excel for Microsoft 365 on Windows, open the Visual Basic Editor from Developer > Visual Basic. If the Developer tab is not displayed, enable it through File > Options > Customize Ribbon. On Mac, the Visual Basic Editor is also available from the Developer tab when enabled; some Mac keyboards require Fn along with a function-key shortcut, so the ribbon command avoids that keyboard difference.

Before running any macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z, and a procedure that changes a range or array may not have a simple reversal. This sample only sorts its local test array, but the precaution is important when adapting code to edit worksheet data.

When to use Quick Sort—and when not to

A custom Quick Sort is most relevant when your VBA code needs to sort an in-memory array and you want control over the implementation. For a simple numeric array, the typed routine above avoids the overhead of working with worksheet cells. For a production routine, consider whether you need more than ascending Long values: a reusable sorter must define how it compares strings, dates, blanks, errors, and mixed types, and should document those rules.

If the data is already on a worksheet, Excel’s built-in sorting tools are often simpler. You can sort a range through Excel’s Data tab, or use the Excel object model’s Range.Sort method from VBA. This sorts worksheet cells and can account for fields and headers; it is a different task from sorting a VBA array. In Microsoft 365, the worksheet function SORT can return a sorted array to the sheet, but it is a worksheet function, not a VBA statement called QuickSort. Availability and behavior differ by Excel version, so use a method supported by the installations that will run the workbook.

For small arrays, even a simple insertion sort can be easier to read and maintain, although it can require O(n²) comparisons. For large datasets, benchmark the complete approach—including moving data between the worksheet and VBA—rather than assuming that the algorithm alone determines speed. If preserving the original order among equal keys matters, choose a stable method or include an original-position tie-breaker.

Common errors and edge cases

“Sub or Function not defined”: Check that the procedure exists, is spelled correctly, and is visible to the calling code. A procedure marked Private cannot be called from outside its module. Also check whether the code was pasted into the correct VBA project.
Parentheses or argument errors: With Call, use parentheses around the whole argument list. Without Call, write the procedure name followed by arguments without that list of parentheses. The two styles should not be mixed.
Type mismatch: QuickSortLong accepts a Long array. A String array or a Variant containing a different array type is not interchangeable. Write a separately typed routine or deliberately design and test a Variant-based comparison strategy.
Invalid bounds: Do not call the procedure with indexes that fall outside the array. For a dynamic array, check that it has been allocated before using LBound and UBound; an uninitialized dynamic array cannot be treated like the initialized test array. Do not pass a last index smaller than the first.
Wrong sorting order: This code sorts ascending. Descending order requires changing the comparison logic, not merely changing the procedure’s name. For text, sorting rules such as case sensitivity and locale can also affect the desired order.

Pay particular attention when adapting the code to sort paired data. Sorting an array of numbers alone is straightforward; sorting scores while keeping names attached requires moving each complete record together, or sorting indexes that refer to the records. Sorting only one column of a worksheet table can separate values from their associated rows, so use a range sort that includes the full table.

Frequently Asked Questions

Is QuickSort a VBA keyword?
No. QuickSort is an ordinary procedure name you can choose, not a reserved VBA keyword or built-in sorting command. It works only when a matching accessible procedure is defined in the project.

Why does VBA say “Sub or Function not defined” for QuickSort?
Usually, the project does not contain an accessible procedure with that name. Add or import the procedure, check its spelling and visibility, and make sure the call’s arguments match its declaration.

Can I use Call with a function?
VBA’s Call statement can invoke a procedure, including a Function, but calling a Function this way discards its return value. If you need the result, use the Function in an assignment or expression instead.

Does Excel have a built-in sort?
Yes. Excel offers worksheet sorting and the VBA object model’s Range.Sort method for worksheet ranges. Microsoft 365 also has the worksheet SORT function. None of these means that VBA includes a built-in Quick Sort routine for arrays.

Can this code sort strings or worksheet cells?
Not as written: it accepts a Long array. A string sorter needs a defined text-comparison rule, while worksheet cells are better handled with Excel’s range-sorting features when appropriate. Adapt the data type and method to the job rather than passing incompatible data to this procedure.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.