VBA Range.Sort with arrays. Alternative for simple use.

[DocAElstein]

VBA Range.Sort with arrays. Alternative for simple use

.

I am considering a fairly simple VBA Array alternative approach to a simple use of the available VBA Range.Sort Method. https://docs.microsoft.com/en-us/off...cel.range.sort

In an important file of mine, I currently use the available VBA Range.Sort Method. https://docs.microsoft.com/en-us/off...cel.range.sort
It lets me sort some long rows of data where the order is based on the values in one of the columns , then for any rows which have the same value in that column , those similar rows would be further sorted by another column. For the VBA Range.Sort Method the syntax terminology would say that I am using pseudo code of this sort of form
MyRange.Sort Key1:=use column1 , __ , Key2:=use columnx , __ , __ , __ ,
You can use up to 3 "Key 's" in the simple single code line.
In other words, if you have some identical values in the columns used to define the final list order, you can sort further using the values in a second column to determine the order in the group of identical values in the first column which you used. In the simple single line use of the available VBA Range.Sort Method, you can do that one more time, so 3 times in total .##
In other words, in that single code line it does , pseudo:
Sort by a column, x: (Key1=x)
Sort by a second column, y (Key2:=y), for those rows with duplicates in it in column x
Sort a final time by a third column, z (Key3:=z), for those rows with duplicates in it in both column x and column y

Here is a simple graphical illustration of what I am talking about: Consider this spreadsheet range:
Before:-
_____[/color ( Using Excel 2007 32 bit )

Row\Col	G	H	I	J	K
13	Was G13	C	Was I13	Was J13	Was K13
14	Was G14	a	Was I14	Was J14	Was K14
15	Was G15	g	Was I15	c	e
16	Was G16	g	Was I16	b	Was K16
17	Was G17	g	Was I17	c	f
18	Was G18	G	Was I18	c	Was K18
19	Was G19	f	Was I19	Was J19	Was K19

Worksheet: Sorting
I will run this simple procedure, ( which is in a normal code module. When I run the procedure the worksheet with Name "Sorting" is active).

Sub RangeSortExample()
 Range("G13:K19").Sort Key1:=Range("G13:K19").Columns("B:B"), Order1:=xlAscending, Key2:=Range("G13:K19").Columns("D:D"), order2:=xlAscending, Key3:=Range("G13:K19").Columns("E:E"), order3:=xlDescending, MatchCase:=False
End Sub

Here is a break down of what that routine does:
The first key ,
Key1:=range("G13:K19").Columns("B:B"), Order1:=xlAscending , MatchCase:=False
, results in this
_____ ( Using Excel 2007 32 bit )

Row\Col	G	H	I	J	K
13	Was G14	a	Was I14	Was J14	Was K14
14	Was G13	C	Was I13	Was J13	Was K13
15	Was G19	f	Was I19	Was J19	Was K19
16	Was G15	g	Was I15	c	e
17	Was G16	g	Was I16	b	Was K16
18	Was G17	g	Was I17	c	f
19	Was G18	G	Was I18	c	Was K18

Worksheet: Sorting
The above screen shows that we have all ordered ( based on the column 2 ,( "B" columns ), of that range , ( which is the column H of the spreadsheet) ). But note, we have duplicates in column 2 with values of g in the last 4 rows.
This next part ,
Key2:=range("G13:K19").Columns("D:D"), order2:=xlAscending, MatchCase:=False
, then sorts those last 4 rows using column 4, (columns "D") of that range values. ( This is the spreadsheet columns "J")
_____ ( Using Excel 2007 32 bit )

Row\Col	G	H	I	J	K
13	Was G14	a	Was I14	Was J14	Was K14
14	Was G13	C	Was I13	Was J13	Was K13
15	Was G19	f	Was I19	Was J19	Was K19
16	Was G16	g	Was I16	b	Was K16
17	Was G15	g	Was I15	c	e
18	Was G17	g	Was I17	c	f
19	Was G18	G	Was I18	c	Was K18

Worksheet: Sorting
In the above screen shot we see that we now have 3 rows containing all gs in the second column, and all cs in the forth column.
The final term is, noting that we are using xlDescending (just for fun ) ,
Key3:=range("G13:K19").Columns("E:E"), order3:=xlDescending
So the last 3 rows are resorted such as to give in those last 3 rows a descending order in the values in the 5^th column in those last 3 rows:
_____ ( Using Excel 2007 32 bit )

Row\Col	G	H	I	J	K
13	Was G14	a	Was I14	Was J14	Was K14
14	Was G13	C	Was I13	Was J13	Was K13
15	Was G19	f	Was I19	Was J19	Was K19
16	Was G16	g	Was I16	b	Was K16
17	Was G18	G	Was I18	c	Was K18
18	Was G17	g	Was I17	c	f
19	Was G15	g	Was I15	c	e

Worksheet: Sorting

Notes:
For simplicity I chose to be case insensitive ( so like g = G __ - _ MatchCase:=False )
Some other argument options are available with the available VBA Range.Sort Method. I will not consider those.
## Furthermore, If you add the options on other lines then use a .Apply code line to apply the sort, then you can have more than 3 "Keys"

I am only considering a comparison to the simple single line using minimal options similar to the worked example

Why do an array alternative?.
I cant think of a particularly good reason: The VBA Range.Sort Method appears to be regarded as a good way to do these things, and often a solution for sorting arrays is based on pasting into a worksheet Range , then using the Range.Sort on that and then capturing the sorted range back into the original range
But I was just interested, out of general interest, to have an alternative: it often occurs that you find bugs in Excel things. The more you have control of "what is going on" , as you do with an array approach, the less is "hidden" from you, such that you have a better chance to change something id something goes wrong.

Various sorting methods, mathematical ways to sort..
The various methods of sorting seem immense and need a good understanding of advanced mathematics. I can't begin to discuss any of that. I will use exclusively the simply "Bubble sort method" , which I will explain from the simplest form, and then adapt to our case

The next posts will go through the simplest Bubble sort theory as applied to a simple 2 dimensional array, and then progress in the following posts to a Function to do similar to the simple case of a VBA Range.Sort Method with the possibility to add keys, for sorting further when values in the initial column used for sorting are identical
For ease of explanation I will refer to the first dimensions in an array as the rows, and the second dimensions as columns , pseudo such that an array could be thought of as a spreadsheet of values. For example a 2 dimensional array of 5 x 2 could be pictorially considered as 5 rows x 2 columns:

r1, c1	r1, c2
r2, c1	r2, c2
r3, c1	r3, c2
r4, c1	r4, c2
r5, c1	r5, c2