One feature noticeably absent from VBA is a built-in function to sort arrays and collections. The bubble sort is a well known technique for sorting groups of elements when no native function exists. With a few minor adjustments, a bubble sort can also be used to sort collections of custom objects.
This is the fourth post in the Tips and Tricks with VBA Classes series. Today, we discuss how to sort objects of a collection class.
As a collection class begins to store a large number of objects, you may reach a point where it needs to be sorted. This could be out of a desire to create a user-friendly feature—such as an alphabetized dropdown list—or to organize data.
Returning to our “CountryDetails” collection class within the example file, the sort feature serves two purposes. First, it alphabetizes the country names in the dropdown list (cell D15), giving us an ordered list when interacting with the spreadsheet. Second, it sorts the save data in the “Data Array CountryDetail” tab by either name or population.
This post continues the use of the “CountryDetails” collection class, building on all previous tutorials in this series. Macros must be enabled upon opening the file.
The Bubble Sort is the most traditional sort option for any group of elements. The approach is commonly applied to arrays and collections of data. The bubble sort is also effective for collections of objects.
The bubble sort works by comparing a collection with itself through a 2-level nested loop (as if it were a copy). Within the 2nd loop, a conditional statement determines if a value within the inner level loop can be placed (in order) within the outer loop. If the test returns true, a temporary copy of the element will be made, placed in its proper place, then the original “misplaced” object is removed.
While the bubble sort is common for one-dimensional arrays—where one value exists within an element—it is possible to apply it to collection classes of objects containing multiple values. This can be done by selecting a specific property to sort.
The following is a flexible and dynamic bubble sort added as a member subroutine to the collection class “CountryCollection”.
Sub Bubble_Sort(propName As String, numericSort As Boolean, Optional ascendingOrder As Boolean = True)
Dim i As Integer
Dim j As Integer
For i = 1 To .Count - 1
For j = i + 1 To .Count
If (numericSort And ascendingOrder And CallByName(.CollectionIndex(i), propName, VbGet) > CallByName(.CollectionIndex(j), propName, VbGet)) _
Or (Not (numericSort) And ascendingOrder And StrComp(CallByName(.CollectionIndex(i), propName, VbGet), CallByName(.CollectionIndex(j), propName, VbGet)) = 1) _
Or (numericSort And Not (ascendingOrder) And CallByName(.CollectionIndex(i), propName, VbGet) < CallByName(.CollectionIndex(j), propName, VbGet)) _
Or (Not (numericSort) And Not (ascendingOrder) And StrComp(CallByName(.CollectionIndex(i), propName, VbGet), CallByName(.CollectionIndex(j), propName, VbGet)) = -1) Then
Set temp = .CollectionIndex(j)
In the example file, the bubble sort will order all the countries by name in ascending order. It will be reflected in the dropdown list in cell D15, and within the string in the “Data Array CountryDetail” tab.
This member subroutine accepts arguments for two required parameters and one optional parameter.
- propName: A required String of the property name to be sorted. Do not include the dot operator — this parameter is a String reference permitting the function to look up specified properties within the collection class.
- numericSort: A required Boolean value specifying if the collection requires a numeric or alphabetic sort. A value of “True” is numeric; a value of “False” is alphabetic.
- ascendingOrder: An optional Boolean value specifying if the array should be sorted in ascending or descending order. A value of “True” will sort ascending; a value of “False” will sort descending. The parameter will default to “True” (ascending order) if no argument is passed.
This method can work “out-of-the-box” with most collection classes after a few minor semantic changes. You will notice highlighted lines 5 and 16 above — these two require minor name-specific changes when added to a new class module. In line 5, the data type of object identifier temp must be changed to the data type name of the object within the collection. In line 16, the collection class CountryCollection must be changed to the specific collection being sorted.
If you exclude the complex conditional statement in lines 10-13, this is a fairly standard bubble sort. The conditional in this procedure permits the use of a single member subroutine to sort based on 4 combinations of criteria: ascending and descending orders for either numeric values or strings. You will notice the conditional organized into 4 lines starting with the “Or” operator. In any single instance, all conditions within a single line returning “True” will trigger the sort operation.
Within each line, a test on the subroutine Boolean parameters numericSort and ascendingOrder will return “True” depending on the specified sort type and order. Two of the lines use the the greater/less than operator for numeric tests, while the other two line include the StrComp function for alpha tests.
In each line, you will also notice an infrequently intrinsic function: CallByName. This function permits flexibility with regards to the collection class’ property used in the test. This allows a property name to be passed as a string value through the parameter propName. The alternative is to reference an exact property within the Bubble_Sort subroutine, but this would make the procedure name specific and take away its flexibility.
There are a number of ways to implement a bubble sort, but it’s important to be cognizant of when it runs in your procedure. Generally, the bubble sort should run every time after adding a new item to a collection, but before using the collection for any output. If multiple items are being added in a procedure (such as the Example File’s load/Class_Initialize procedures), the bubble sort should run after all items are added. Multiple runs of a bubble sort—such as being called from within a loop—will be redundant. Likewise, if it’s called prior to a new item being added, that new item will not be sorted.
The example file implements the bubble sort procedure as a class method. This appends the procedure to the class, making it easily accessible if the class is exported to another project. The method, itself, is called within the save subroutine (see line 15 below), given that this is the final process before data gets printed in the spreadsheet.
For demonstration purposes, the example file includes all 4 possible setting combinations for the member subroutine. Only one is active by default — the rest are commented out. You can test all 4 by removing/adding comment blocks so each individual call is active.
Dim saveSheet As Worksheet
Dim i As Integer
Dim j As Integer
Set saveSheet = ThisWorkbook.Worksheets("Data Array CountryDetail")
If CountryDetails Is Nothing Then
Call Set_Object ' Calls subroutine to set object in Data_Management module
' Each possible implementation of the bubble sort is below. You can activate/deactivate to test by adding/removing the comment block.
Call .Bubble_Sort("countryName", False, True) ' countryName, alpha sort, ascending order
'Call .Bubble_Sort("countryName", False, False) ' countryName, alpha sort, descending order
'Call .Bubble_Sort("countryPop", True, True) ' countryPop, numeric sort, ascending order
For i = 1 To .Count ' Loop through collection to build arrays on individual rows
saveSheet.Cells(i, 1).Value = "CountryDetails|" & .Item(i).countryName & "|" & .Item(i).countryPop & "|" & .Item(i).countryLandArea
Sheet1.Range("Record_Count_Array").Value = .Count
Call Update_Validation_Cell ' Call procedure to add new country to dropdown list
- See our post providing more details on bubble sort and its use with other VBA data structures.
- If you find that the order of custom objects is changing, but not as intended, check to make sure the property within the class is set to the correct data type.
- If you choose to keep bubble sort within a standard module, it must be name specific. That means it will only sort the collection with that specific name — not ideal if multiple versions of that same collection exist in memory. By including the bubble sort as a method within the class module, the procedure will not be name dependent.