VBA’s Collection object has no built-in sort method. To sort a collection, copy its items into an array, sort that array, and put the values back; the example below sorts numeric items in ascending order while retaining the original collection object.
Why a VBA Collection Does Not Sort Itself
A VBA Collection is an ordered container: items stay in the order in which they were added, and you can retrieve an item by its one-based position. It supports operations such as adding, removing, and counting items, but it does not expose a method such as Sort. In other words, adding values in a particular sequence does not cause VBA to reorder them automatically.
This design is useful when insertion order matters, but it means a sort requires a separate strategy. The usual approach is to copy the values out, sort them using code or another sorting feature, and then use the sorted result. Sorting a collection is therefore not the same as sorting a worksheet range, which has Excel’s built-in Sort command.
Also distinguish a collection’s position from its optional key. A position is the item’s current index, such as item 1 or item 2. A key is a string identifier supplied when an item is added. The collection’s public interface does not provide a way to enumerate those keys, which affects whether a general-purpose routine can preserve them while reordering items.
How the Array-and-Rebuild Method Works
The collection-to-array method separates the work into three stages. First, copy each collection item into a temporary array. Second, sort the array using comparisons that match the kind of data. Finally, remove the collection’s existing items and add the sorted values in sequence. Doing the sorting before removing anything is useful: if a comparison fails, the original collection has not yet been cleared.
The code below uses insertion sort. It takes each value in turn and moves larger earlier values one position to the right until the new value reaches its place. This algorithm is straightforward and stable: values that compare equal retain their original relative order. Its running time can grow roughly with the square of the item count, so it is best suited to small or moderate collections rather than very large data sets.
This example is deliberately limited to numeric-compatible values. It rejects objects, errors, Null values, and values that VBA does not recognize as numeric. Numeric strings such as "12" are compared as numbers, not as text, while the value placed back into the collection remains the original value. If exact numeric types or locale-sensitive numeric strings matter, use values of a consistent numeric type and adapt the comparison logic to the requirements.
Numeric Collection Sorting Code
Use this procedure in a standard VBA module. It sorts ascending and changes the items in the passed collection. Because it removes and re-adds items without keys, it does not preserve keyed entries; see the key discussion below before using it on a collection whose keys matter.
Public Sub SortNumericCollection(ByRef items As Collection)
' Sort numeric-compatible values in ascending order.
Dim itemCount As Long
Dim values() As Variant
Dim i As Long
Dim j As Long
Dim keyValue As Variant
Dim sortedItems As Collection
itemCount = items.Count
If itemCount < 2 Then Exit Sub
ReDim values(1 To itemCount)
' Validate and copy before changing the collection.
For i = 1 To itemCount
If IsObject(items(i)) Then
Err.Raise vbObjectError + 1000, "SortNumericCollection", _
"Object items are not supported."
End If
If IsError(items(i)) Or IsNull(items(i)) Then
Err.Raise vbObjectError + 1001, "SortNumericCollection", _
"Error and Null items are not supported."
End If
If VarType(items(i)) = vbBoolean Or Not IsNumeric(items(i)) Then
Err.Raise vbObjectError + 1002, "SortNumericCollection", _
"Every item must be numeric or a numeric string."
End If
values(i) = items(i)
Next i
' Stable insertion sort; convert only for numeric comparison.
For i = 2 To itemCount
keyValue = values(i)
j = i - 1
Do While j >= 1
If CDbl(values(j)) <= CDbl(keyValue) Then Exit Do
values(j + 1) = values(j)
j = j - 1
Loop
values(j + 1) = keyValue
Next i
' Rebuild the same collection object in sorted order.
Do While items.Count > 0
items.Remove 1
Loop
For i = 1 To itemCount
items.Add values(i)
Next i
End Sub
For example, if a collection contains 8, 3, and 5 in that order, the procedure leaves it containing 3, 5, and 8. The procedure returns no separate result because it changes the supplied collection. An empty collection and a collection with one item need no work, so the early exit handles both without attempting to create or sort an array.
The validation uses VBA’s IsNumeric function, and comparisons convert values with CDbl. That is suitable for ordinary numeric sorting, but it is not a promise of exact ordering for every possible numeric representation. For example, floating-point values can have representation limits, and text that looks numeric can be interpreted according to regional settings. For financial or precision-sensitive data, choose a suitable numeric type and comparison strategy rather than relying on conversion to Double.
Running the Macro Safely
In Excel for Microsoft 365 on Windows, open the Visual Basic Editor, insert a standard module, and paste the procedure there. Create or populate a Collection in a calling procedure, then pass it to SortNumericCollection. For example, a caller can declare Dim values As Collection, set it with Set values = New Collection, add numeric values, and call SortNumericCollection values. The collection must be an initialized object; passing an uninitialized variable will cause an error.
Save a copy of the workbook before running a macro. VBA macro actions cannot be undone with Ctrl+Z, and rebuilding a collection changes its contents immediately. Save a workbook containing VBA as a macro-enabled Excel workbook (.xlsm) if you need to keep the code. Excel for Mac also supports VBA and the Collection object, though editor access and some automation capabilities differ; this example does not depend on Windows-only automation components.
When testing, inspect the collection’s items after the call rather than expecting a worksheet range to change. A VBA collection is held in memory unless your code separately writes its values to cells. If the procedure raises an error during validation, it raises it before removing existing items, so correct the unsupported value and run it again.
What Happens to Collection Keys and References?
A collection can be populated with keyed items using the optional Key argument to Add. The sample procedure rebuilds items using only their values. Consequently, any keys are discarded. Since the collection does not expose a public indexed key list, a generic routine cannot simply read each key alongside each item and restore the same key-to-value associations after sorting.
If keys are important, do not run the sample unchanged. Keep the key and value together in a separate structure you control, such as a class with fields for both, or maintain a parallel array of keys and values at the time items are added. Then sort the paired records and rebuild the collection with the intended keys. Be explicit about the meaning of a key: if it identifies a particular record, sorting should move that record and its key together, not assign keys based only on the new position.
For object items, sorting references can be done by storing object references in an array and comparing a property of each object, such as a name or date. The numeric sample rejects objects because VBA cannot infer which property should define their order. Rebuilding with the same object references does not clone those objects, but code that removes items or changes object properties can still affect other parts of a program that use them.
Sorting Text, Objects, or Descending Values
Text sorting needs a text comparison rule. Decide whether uppercase and lowercase should be treated as equal, and whether the intended order is alphabetical or based on a particular locale. In VBA, StrComp can compare two strings using an explicit comparison mode, such as vbTextCompare for case-insensitive comparison or vbBinaryCompare for binary comparison. Replace the numeric comparison in the sorting loop with the chosen text comparison; do not convert text values to numbers.
For descending numeric order, reverse the comparison condition in the insertion-sort loop. The ascending version shifts an earlier value when it is greater than the value being inserted. For descending order, shift it when it is less. Keep the chosen comparison consistent throughout the algorithm, especially if equal values should remain stable.
For objects, define a comparison using a property that exists on the object type. For instance, records might be ordered by a date property and then by a name property to break ties. VBA does not know whether a collection of objects should be ordered by a name, date, identifier, or some other field, so that rule must be supplied by the program. A custom class or a dedicated array of sortable records is often easier to maintain than trying to make one procedure sort every possible collection type.
Choosing Between a Collection, Array, and Worksheet Sort
Keep a collection when insertion order and convenient addition or removal are useful, and the number of items is manageable. An array is often a better working structure when sorting, indexing, or processing many values is central to the task. You can sort an array and then create a collection if later code specifically requires one, rather than repeatedly rebuilding the collection during processing.
If the data already lives in worksheet cells, Excel’s worksheet sort is usually more appropriate than copying it into a VBA collection. In Excel for Microsoft 365 on Windows, select the data and use Data > Sort, or use the sort controls on the Data tab. A worksheet sort can keep rows together when the whole data range is selected and sorted by a column. A collection sort only reorders its own items; it does not know about related worksheet columns.
A dictionary is useful for looking up an item by key, but it is not a substitute for a sorted collection when you require an explicit sorted sequence. Likewise, Excel’s SORT worksheet function sorts an array or range result in supported Excel versions; it does not add a sort method to a VBA collection. Choose the container for the job: collections for ordered item management, dictionaries for keyed lookup, arrays for direct indexed processing, and worksheet sorting for tabular data.
Frequently Asked Questions
Can I use Collection.Sort in VBA?
No. VBA’s built-in Collection object has no Sort method. Copy the items to a sortable structure, apply a comparison rule, and use the sorted sequence where it is needed.
Does the numeric example sort numbers as text?
No. It compares values after converting them with CDbl, so numeric values such as 2 and 10 are ordered numerically, with 2 before 10. It accepts numeric strings but retains their original values when rebuilding the collection.
Can I sort a Collection from largest to smallest?
Yes. Change the insertion-sort condition so that smaller earlier values move right instead of larger ones. The correct condition depends on the comparison rule and data type being sorted.
Will sorting preserve Collection keys?
Not with the example shown. It removes and re-adds values without their keys, and the public Collection interface does not provide a way to enumerate existing keys. Store keys alongside values in a structure you control if they must move together.
Is insertion sort suitable for a very large Collection?
Usually not. It is easy to understand and works well for modest item counts, but its work grows quickly as the collection grows. For large data sets, sort an array with a more efficient algorithm or use Excel’s sort facilities if the data is already in a worksheet.
Related
- PowerPoint Table Diagonal Split Cell: Practical Methods
- Data Validation and Quality Assurance in Excel
- Power Query Remove Leading Zeros Without Losing IDs
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Show UserForm: Open a Form in ExcelTo show an Excel VBA UserForm, call its Show method, usually from a standard module: UserForm1.Show. In Excel…
- Pivot Table VBA: How to Hide SubtotalsTo hide subtotals in an Excel PivotTable with VBA, set the Subtotals property to twelve false values for…
- VBA Global Dictionary: Scope, Setup, and Code ExamplesVBA has no built-in global dictionary type, but in Excel for Microsoft 365 on Windows you can create…
- How to Concatenate Strings in VBA Using & and JoinIn VBA, concatenate strings by joining them with the ampersand operator (&), such as fullName = firstName &…
- Excel VBA: Find a Cell with Specific Text in a RangeTo find a cell containing specific text with Excel VBA, use the Range.Find method. Set options such as…
- VBA User Input Box: InputBox, Validation, and ExamplesA VBA user input box is a small dialog that asks someone to enter text, a number, or…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.