top of page
  • John

Sorting Objects in VBA

Updated: Sep 12, 2023

This post follows-on from the previous Sorting Strings in VBA post. Again this sort uses the merge sort algorithm but this time the sort will work for Objects, either those defined by Class Modules in your VBA project or built-in Objects such as Worksheets in Excel, Documents in Word etc.


In the previous post I gave some example timings - I'm not going to do so this time as the timing is very dependent on how the Objects are being sorted (in the Compare() Function ... keep on reading!) along with other factors such as the performance of the device you are using. Just to say that using the merge sort algorithm is quick.


And just to note that this code will work to sort both static and dynamic arrays of Objects, and works whether the lower bound is 0 or 1 (or anything else). It will perform either an ascending or descending sort and (if Strings are involved in the sort as they are in these examples) will perform either a case-sensitive or a case-insensitive sort.


So, on with the code. This needs to be added to a standard Module, I usually name this SortObjects but replace 'Object' with the type of Object to be sorted.

Now the main difference here compared to sorting Strings (if you read the previous post) is the addition of the Compare() Function which you need to customise based on the nature of the Objects being sorted and the nature of the sort you want carried out. Just to be clear: you do not need to change the Sort() or SortInternal() methods.


Here is an example of a Compare() Function:

This will compare any two Objects using their Name property. So this could be Worksheets in Excel, Documents in Word, Slides in PowerPoint ... and it would work for Objects created from Class Modules if they have a Name property e.g. you might have a 'Customer' Object with a Name property.


The key consideration of the Compare() Function is that it should return a specific value depending on whether the two Objects being compared are the same (that is, not that they are the same Object but that the values that they are being compared by are the same ... value equality not reference equality) or one is considered 'less than' or 'more than' the other. Compare() should return:

  • 0 if the two Objects being compared are the same

  • -1 if the the first Object is 'less than' the second Object

  • 1 if the the first Object is 'more than' the second Object

... and, handily (not a coincidence!), this is exactly what the VBA StrComp() Function does for Strings.


And here's an example to use it:

You can tell from the above line of code that, in this example, I have put the code to perform the sort into a (standard) Module named SortWorksheets, it is sorting an array of Worksheet Objects called aoWorksheets, the sort is ascending and it is a case-sensitive sort. Nice and simple, one line of code, re-usable throughout your code base if you need to sort different arrays or sort in different orders.


I guess I need to clarify one point: this sorts the Objects (e.g. Worksheets) in the array of Worksheets ... it doesn't sort the Worksheets in the Excel user-interface. And just the same if you sort Documents in Word or Slides in PowerPoint ... this is sorting the array only.


So, what if the Object has multiple properties that you want to compare by? Here's an example for Worksheets in Excel that compares firstly by the number of rows used (i.e. in the UsedRange) and, if they are the same, then compares by the number of columns used.

Note that the value passed in for compareMethod is unused ... we're comparing numbers here ... hence why it is an Optional parameter in Compare() and in both Sort() and SortInternal() (in the previous Sorting Strings in VBA post it was required because in that post we were, naturally, always sorting Strings and so you should always be specific about the method of String comparison).


What if you sometimes want to sort the same type of Object but sort with different criteria? Maybe you have an array of Worksheets that you sometimes want to sort by name and at other times by the number of used rows and columns? The (bad) solution is to add another standard Module with duplicates of the Sort() and SortInternal() methods and then have different Compare() methods for each. The (better) solution is to use the one standard Module, add an Enum to it such as:

Add a parameter to each of the Sort(), SortInternal() and Compare() methods such as

And update the code of the Sort() and SortInternal() methods to pass the relevant value for eSortType. Then the Compare() Function can test the value for eSortType and perform the relevant sort ... so, then, this is your code:

And then you can call it like

or


To change the nature of the sort or to sort another type of Object, then you just need to change the names of the Enum members (or add more Enum members) and then change the Compare() Function to provide the appropriate return value - so here is one last example ... this time using Word ... with the exact same Sort() and SortInternal() methods, you can sort paragraphs either alphabetically or by word count (then length if they have the same word count). This would be your Enum:


And this would be your Compare() method:

And then you could call it like

Or

Happy sorting!

0 comments

Recent Posts

See All

Comments


bottom of page