How to Sort a Two-Dimensional Array in VBA with Custom Columns

Discover techniques to efficiently sort a one-dimensional array that contains two-dimensional data in VBA. Learn how to sort using specific columns with practical code examples.
---
This video is based on the question https://stackoverflow.com/q/71853164/ asked by the user 'Jiri Zaloudek' ( https://stackoverflow.com/u/1092050/ ) and on the answer https://stackoverflow.com/a/71855513/ provided by the user 'FaneDuru' ( https://stackoverflow.com/u/2233308/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: sort two dim array which is declared as one dim array and inserted values as array()

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/licensing
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/by-sa/4.0/ ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/by-sa/4.0/ ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Sort a Two-Dimensional Array in VBA with Custom Columns

Sorting arrays can be a daunting task, especially when they are structured in a way that isn't directly supported by native functions in Excel or Visual Basic for Applications (VBA). Many users often face challenges with two-dimensional arrays, particularly when those arrays are declared as one-dimensional variants. In this post, we’ll tackle a real-world scenario where we sort a jagged array by multiple columns using VBA.

The Problem

Imagine you have a one-dimensional array in VBA that contains several arrays—making it a jagged or multidimensional array. You need to sort this array based on specific criteria, namely the values in certain columns of those inner arrays.

Here is a simplified example of the problem:

[[See Video to Reveal this Text or Code Snippet]]

In this case, the objective is to sort someArray:

First by the second column (of type String).

If there are matching values, then by the third column (of type Long).

The Solution

The code provided below demonstrates how to effectively sort such an array. We will use a custom sorting function that can handle sorting based on multiple columns.

Step-by-Step Code Breakdown

Here’s the entire solution, broken down for better understanding:

1. Define Subroutine and Initialize the Array

Start with defining the main subroutine to initialize the array:

[[See Video to Reveal this Text or Code Snippet]]

2. Sort Function

Next, create a function that sorts the jagged array:

[[See Video to Reveal this Text or Code Snippet]]

3. Bubble Sort Helper

Finally, you also need a bubble sort function to sort the data within the column:

[[See Video to Reveal this Text or Code Snippet]]

Conclusion

This approach allows you to sort a complex jagged array in VBA effectively based on multiple criteria. The sorting function can be customized further to handle additional columns if needed.

As always, feel free to modify the code snippets and test them in your VBA environment. If you encounter any challenges or have questions, I'm here to help!

Видео How to Sort a Two-Dimensional Array in VBA with Custom Columns канала vlogize