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
- Power Query Editor: What It Is, How It Works, and Why
- Excel Data Validation Icon: What It Looks Like and Does
- Data Validation Multiple Selection in Excel: What Works
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Boolean: True, False, and How to Use ThemYes—Excel VBA has a built-in Boolean data type for values that are either True or False. Declare a…
- Excel VBA Input Box to Select a Row by NumberTo select a worksheet row by its number, use VBA’s Application.InputBox with Type:=1, validate the entered number, then…
- VBA Pivot Table Ranges: How to Set and Resize ThemIn VBA, a PivotTable’s displayed range is read with PivotTable.TableRange2, while its source data range is set through…
- Excel VBA Else If: Syntax, Examples, and AlternativesIn Excel VBA, the correct keyword for a chain of conditions is ElseIf, written as one word—not Else…
- VBA Dictionary Class Modules: A Practical How-ToTo use a class module with a VBA Dictionary, define a class to represent one record, create an…
- Excel VBA Copy: How to Copy Cells, Sheets, and FilesTo copy cells with Excel VBA, use Range.Copy with a destination, such as Worksheets("Sales").Range("A1:D20").Copy Destination:=Worksheets("Summary").Range("A1"). This copies the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.