VBA Sort String Array: Methods, Code, and Examples

VBA & macros · Updated November 18, 2026

VBA has no built-in general-purpose routine for sorting a string array, but you can sort one with a short insertion-sort procedure. The example below sorts a one-dimensional String array in place; its default comparison is case-insensitive, and you can select a case-sensitive comparison when calling it.

A simple VBA string-array sort

Put this procedure in a standard VBA module. It uses insertion sort: each value is moved left until it belongs before the values already processed. The array keeps its existing lower and upper bounds, and equal strings are left in their original relative order.

Option Explicit

' Sort an initialized, one-dimensional String array in place.
Public Sub SortStringArray(ByRef items() As String, _
                          Optional ByVal compareMode As VbCompareMethod = vbTextCompare)
    Dim first As Long
    Dim last As Long
    Dim i As Long
    Dim j As Long
    Dim key As String

    first = LBound(items)
    last = UBound(items)

    For i = first + 1 To last
        key = items(i)
        j = i - 1

        Do While j >= first
            If StrComp(items(j), key, compareMode) <= 0 Then Exit Do
            items(j + 1) = items(j)
            j = j - 1
        Loop

        items(j + 1) = key
    Next i
End Sub

The routine expects an allocated, one-dimensional array declared as String. For example, a fixed-size array with bounds 0 To 4 or a dynamic String array that has already been ReDimmed is suitable. An unallocated dynamic array has no bounds for LBound and UBound to return, so initialize it before calling the procedure. A one-element array is fine: the loop simply has no work to do.

Call the procedure and check the result

For example, declare and fill a string array, then pass it to the sorter. This sample uses a nonzero lower bound to show that the procedure does not assume arrays start at index zero.

Public Sub ExampleSort()
    Dim names(1 To 5) As String

    ' Fill the array before sorting it.
    names(1) = "pear"
    names(2) = "Apple"
    names(3) = "plum"
    names(4) = "banana"
    names(5) = "Apple"

    ' Uses the procedure's default, case-insensitive comparison.
    SortStringArray names
End Sub

After the call, the values are Apple, Apple, banana, pear, plum. The two identical Apple entries are both retained; sorting changes their positions, not the number of elements. The procedure changes the original array because its parameter is passed ByRef. It does not create a second sorted copy.

Before running a macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z. To run a macro in Excel for Microsoft 365 on Windows, use Developer > Macros, select the macro, and choose Run. On Mac, use Developer > Macros as well; if the Developer tab is hidden, enable it in Excel’s ribbon settings. Do not enable macros from an untrusted workbook just to try its code.

Choose case-sensitive or case-insensitive ordering

The optional compareMode argument controls how StrComp compares each pair of strings. The default, vbTextCompare, treats letter case as equivalent for ordering purposes. That is often convenient for names and labels because uppercase and lowercase versions are not separated into distinct groups. The exact order of accented letters and other characters can depend on the text-comparison rules of the system locale.

For a binary comparison, pass vbBinaryCompare. This distinguishes uppercase and lowercase according to character values, so a result such as Apple versus apple is not treated as equal. This can be useful when case itself matters, though the resulting order may not match a person’s preferred dictionary order.

Public Sub ExampleBinarySort()
    Dim words(0 To 3) As String

    ' A small sample containing mixed capitalization.
    words(0) = "pear"
    words(1) = "Apple"
    words(2) = "apple"
    words(3) = "Banana"

    ' Request a case-sensitive, binary comparison.
    SortStringArray words, vbBinaryCompare
End Sub

Use the named constants rather than relying on numeric values for comparison modes. Also note that sorting does not normalize the strings: it will not trim spaces, convert everything to lowercase, remove accents, or change punctuation. If those transformations are required, decide whether to alter the original values or compare transformed keys while preserving the original text.

How the insertion-sort method works

The procedure starts at the second array element and stores it in key. It compares key with earlier elements, shifts larger elements one position to the right, and inserts key into the resulting gap. It repeats this process for each remaining element. StrComp returns a negative value when its first argument sorts before its second, zero when they compare equal, and a positive value when it sorts after it. The test for a value less than or equal to zero stops shifting when the preceding item already belongs before key, or compares equal.

Insertion sort is simple and stable: when two values compare as equal, their original order is preserved. Its main limitation is speed. In the worst case, such as a list supplied in reverse order, it can make a number of comparisons and shifts proportional to the square of the number of items. It is a sensible choice for short lists, small lookup tables, or code where clarity matters more than sorting a very large collection. For thousands of strings or repeated large sorts, consider a more efficient algorithm or Excel’s built-in sorting facilities.

Sorting values read from worksheet cells

A common point of confusion is that a range read into a Variant is not a one-dimensional String array. For a multi-cell range, assigning Range.Value to a Variant produces a two-dimensional Variant array, even when the range has only one column. You cannot pass that value directly to SortStringArray, whose parameter is a one-dimensional String array.

For a single-column range, copy its values into a typed array first. This example assumes the cells contain text or blanks, not Excel error values. CStr converts ordinary values to strings; an error value needs to be checked separately before conversion.

Public Sub SortNamesFromCells()
    Dim source As Variant
    Dim items() As String
    Dim i As Long
    Dim rowCount As Long

    ' Read a single-column range; multi-cell ranges become 2D Variants.
    source = ThisWorkbook.Worksheets("Sheet1").Range("A2:A6").Value
    rowCount = UBound(source, 1)
    ReDim items(1 To rowCount)

    ' Convert each cell value into the one-dimensional String array.
    For i = 1 To rowCount
        items(i) = CStr(source(i, 1))
    Next i

    SortStringArray items
End Sub

This example sorts the in-memory copy only; it does not write the sorted results back to the worksheet. To display them, write the array to an output range, taking care to put it in the two-dimensional shape Excel expects for a multi-cell range. If the source range may contain errors such as #N/A, test each Variant value with IsError before calling CStr and decide how those errors should be handled.

Common problems and how to avoid them

  • Subscript out of range: The array may be unallocated, or code elsewhere may be using an index outside its bounds. ReDim a dynamic array before sorting and use LBound and UBound rather than assuming a particular starting index.
  • Type mismatch when calling: The sample procedure expects a String array, not a Variant array returned by Range.Value, an Array(…) expression, or a two-dimensional array. Copy or convert the values to a one-dimensional String array first.
  • Unexpected capitalization order: vbTextCompare is the default. Pass vbBinaryCompare if uppercase and lowercase must be distinguished, and remember that binary order is not the same as ordinary dictionary order.
  • Blanks appear in the results: A blank string is still an array element. The sort moves it according to the comparison rules; it does not delete empty entries. Remove or skip blanks separately if that is the desired behavior.
  • The worksheet did not change: The routine sorts only the array passed to it. If the array came from cells, explicitly write the sorted values back to a range.

Also check whether the data contains leading or trailing spaces. For example, a string beginning with a space may sort before a name beginning with a letter. If spaces should be ignored, trim values deliberately and consider whether changing the stored text is acceptable. Sorting and cleaning data are separate operations, so keeping them separate makes the result easier to explain and maintain.

Alternatives for larger lists or worksheet data

For worksheet cells, Excel’s Data > Sort command is often more suitable than transferring values into VBA. It can sort a selected range and keep adjacent columns together when the range is selected correctly. Review the selection and any header-row choice before sorting; sorting only one column can detach names from related records.

If you want a formula result rather than a macro, Microsoft 365 and Excel 2021 include the SORT function. Assuming the names are in A2:A20, enter =SORT(A2:A20) in an empty cell. The sorted list spills into cells below, and the source range remains unchanged. SORT is a worksheet function, not a VBA array method, and it does not let you choose vbBinaryCompare as an argument.

For a large in-memory VBA array, a quicksort or mergesort implementation can reduce typical sorting work compared with insertion sort, but it adds code and choices around comparison behavior, duplicates, and recursion. VBA also provides the ArrayList.Sort method through .NET interoperability in some Windows configurations, but it introduces a dependency and is not a universal replacement across Excel environments, particularly Mac. For portable VBA, a self-contained sorting routine is easier to move between computers.

Frequently Asked Questions

Does VBA have a built-in function to sort a string array?
No general built-in VBA function sorts an arbitrary String array in place. You can write a sorting procedure such as the one above, use a suitable external object with portability caveats, or sort worksheet data with Excel’s own tools.

Does SortStringArray change the original array?
Yes. It receives the array ByRef and rearranges its elements directly. Make a separate copy first if you need to preserve the original ordering.

Can I sort an array returned by Range.Value?
Not directly with this procedure. A multi-cell range returns a two-dimensional Variant array. Convert the relevant values into a one-dimensional String array, sort that array, and write the results back if needed.

How do I sort strings without regard to capitalization?
Use vbTextCompare, which is the sample procedure’s default. To make the choice explicit, call SortStringArray items, vbTextCompare.

Can this code sort numbers stored as strings numerically?
No. It sorts text, so values such as “100” and “20” are ordered as strings rather than as numbers. Convert numeric text to numeric values and sort numerically if numeric magnitude is what matters.

Related

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.