Bubble Sort for Arrays and Other VBA Data Structures

The bubble sort is a commonly used technique for sorting simple arrays and collections. It can also be used to sort through other common VBA data structures, such as two-dimensional arrays, collections of custom objects, and dictionaries.

Overview

This post provides an overview of user-defined functions for sorting common VBA data structures with the bubble sort technique. These functions are set up for easy implementation, requiring minimal adjustments within the function. A description for each method provides details on how to set up these functions in your project.

The concept of the bubble sort is the same regardless of the data structure you are trying to sort. A two level loop analyzes the proper place of all elements in the structure. If a an element fits within an order, a copy is made, inserted in the new space, and removed from the old space.

While the general approach is the same for all data structures, each type brings on a set of different rules that require semantic changes and some other slight variations. For example, an element in a collection cannot be modified, so the bubble sort removes the old and insert a new, whereas an array permits us to simply update the element. Dictionaries post their own set of challenges as an error will fire if a duplicate key exists. This post explains these variations and discusses how to avoid common issues.

Example File

This post’s example file provides a working scenario of stock data. A list (starting at row 30) shows unsorted data reflecting stock ticker, stock price, and company market cap. In the tables located above the list (row 5 and across), the data can be sorted using different data structures. The button above each column will sort the data for that specific column, and a checkbox above the buttons permit you to toggle ascending or descending order. Columns separated by a thick blue bar are sorted individually, while columns separated by 2 lines are sorted as a table of data.

The example file demonstrates the bubble sort on 5 different data structures.
The example file.

You can download the example file here. Macros must be enabled for this file to work.

The user-defined functions for each data structure are displayed and explained in the following sections. You can also look at the example file’s VBA editor to see the full implementation. Functions and implementation technique are grouped together in their own module.

One-Dimensional Array

Within the example file, this function can be found in the standard module “Interface_One_Dim”.

Parameters

  • sortArray: A required Variant of array to be sorted. Only arrays of the Variant data type can be passed as arguments through a function.
  • numericSort: A required Boolean value specifying if the array needs 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.

Description

Excluding the complex conditional statement, this is a standard bubble sort. It will sort a one-dimensional array, which is only one chain of data, so this is not ideal for large tables or sets of data. A two dimensional array (discussed below) would be useful for more complex datasets.

Want to learn more about one-dimensional arrays? Read this post.

Two-Dimensional Array

Within the example file, this function can be found in the standard module “Interface_Two_Dim”.

Parameters

  • sortArray: A required Variant of array to be sorted. Only arrays of the Variant data type can be passed as arguments through a function.
  • searchCol: A required Integer of the index in the 2nd dimension that should be sorted.
  • numericSort: A required Boolean value specifying if the array needs 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.

Description

This function can sort entire sets of data, regardless of array height (rows) or width (columns). The sort index is required as an argument through the searchCol parameter. This is based off of the second dimension of the array — if an array is declared as (0 to 3, 0 to 9) and you need to sort the fifth column, the number 4 should be passed as an argument (remember that most arrays start at zero, not one).

This array can also be used to sort ranges. This is done in the example file through Sort_Mkt_Two_Dim_Array subroutine. Assigning the range to an array variable declared as a Variant with no bounds permits the range to be passed as an argument (as an array). One important thing to remember in this situation is how ranges are converted into an array: unlike typical protocol—where array bounds begin at zero—ranges will default to 1 as the lower bound. So in this situation, if you want to sort the fifth column, you should pass 5 as an argument through the searchCol parameter.

Want to learn more about 2-dimensional arrays? Read this post.

Standard Collection

Within the example file, this function can be found in the standard module “Interface_Collection”.

Parameters

  • sortCollection: A required Collection to be sorted.
  • numericSort: A required Boolean value specifying if the array 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 collection 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.

Description

Again looking past the complex conditional, this is a basic bubble sort. One of the challenges with a collection is that values cannot be updated after they are added. To get around this, the function stores the sort candidate in a temporary variable, removes it from the collection, then ads it back into the collection in its proper place.

Want to learn more about collections? Read this post.

Collection Class

Within the example file, this subroutine can be found in the class module “Portfolio”.

Parameters

  • 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 class needs 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.

Details

Note: to implement this method in your collection class, you must change the object data type name (Stock) in line 5, and the collection name (Portfolio) in line 16.

This bubble sort is unique in that it’s a member subroutine of a collection class (not a user-defined function). To permit full flexibility for users, the intrinsic CallByName function allows a property name to be passed as a String value through the propName parameter. Otherwise, this is a fairly straightforward bubble sort consistent with the standard collection sort (above).

Keep in mind that this sort will also move all other properties within each object of the collection. Think of the collection in a table format: with each object being a row, and each property being a column. In that regard, it’s the same treatment as the bubble sort for multi-dimensional arrays.

This subroutine is accessed differently than the other functions. Instead of assigning the procedure to a variable within the standard module, a method call is used. Simply referencing properties after the call will reflect the sort.

Want to learn more about Collection Classes? Read this post. You can also read more details about sorting objects of a collection classes.

Dictionaries

Within the example file, this function can be found in the standard module “Interface_Dictionary”.

Parameters

  • sortDictionary: A required Dictionary to be sorted.
  • numericSort: A required Boolean value specifying if the array needs 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 dictionary 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” if no argument is passed.
  • sortByKey: An optional Boolean value specifying if the dictionary should be sorted by key, or by item. A valueof “True” will sort by key; a value of “False” will sort by item. The parameter will default to “False” if no argument is passed.

Description

A dictionary data structure presents a unique challenge for the bubble sort. First, it’s difficult to pull both the key and the item within a two level loop — this requires a two-level For Each loop (as opposed to a basic For loop), which does not contain its own counting mechanism. Second, dictionaries do not permit the key to be updated (only read), so the bubble sort structure would need to be similar to a collection. However (and third), no duplicate key for a dictionary can ever exist, so we couldn’t rely on the collection’s add/remove technique as it requires a duplicate key to exist, albeit for a only 3 lines of code (VBA doesn’t care — it will still fire the error).

Getting around these limitation requires a bit of VBA trickery. Instead of attempting to apply a bubble sort directly to a dictionary, we convert the dictionary to a 2D array (lines 17 to 21), apply a bubble sort (lines 29 to 43), then convert the 2D array back into a dictionary (line 42). In doing this, we know that the 2D array will only have 2 columns — one for the key, the other for the item. The procedure can be set up so the key will always be assigned column 0, while the item is always column 1. This also permits us to specify whether or not we should sort by key or item (lines 22 to 26).

Want to learn more about dictionaries? Read this post.

Other Notes

  • The bubble sort is not the only sorting alogrithum available. With extremely large data sets, such as complex 2D arrays with many columns, other sort algorithums may be more efficient. Generally the bubble sort is the most flexible, but not the fastest.

Leave a Reply