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

[DocAElstein]

Recursion routines: Sub Bubbles , and Sub BubblesIndexIdeaWay

Sub Bubbles
Because recursion routines , ( at least for me ) are always difficult to understand, we will initially do a recursion routine without the new Index idea way, but which will use hindsight from the previous posts to make it in a way that can be easily modified to become Sub BubblesIndexIdeaWay But it actually the modifications are very minor, and actually only in the Calling routine Sub Call_Sub_Bubbles()
I suppose I am just to trying to consolidate the understanding of the recursion code, before introducing the new “Index way idea” form of it.
In this routine, Sub Bubbles , I will already use a global variable, which I have more recently introduced specifically for the Index way idea, Rs()
In previous coding I have passed a string of the row indices , strRws = “ 1 3 2 5 6 7 “ , between different copies of the recursion routines. Because I need to use Rs() in the next routine , Sub BubblesIndexIdeaWay , I will make use of it now to ease the transition to the Index way idea.
But important : I later need Rs() in , Sub BubblesIndexIdeaWay to house all indices in a reordered way to allow use of the App.Index formula. So I can’t do away with my strRws, which is used to pass between recursion routine copies the sub set of rows to be sorted for when rows in the column currently last sorted had duplicate values.
So I mention Rs() a bit, but don’t really use it much..
Only in the case of the first copy of the recursion routine, the previous string , strRws , and R(s) , fulfil the same basic purpose of housing indices for the current row order in the current running copy of the recursion routine

So for Sub Bubbles, or rather Sub Call_Sub_Bubbles() , we need ( only ) the global variable R(s).
But it does no harm to include all the variable needed for this and Sub BubblesIndexIdeaWay, at the top of the code module,

Option Explicit
Dim Cms() As Variant, Rs() As Variant      ' "Horizointal Column" Indicies  , "Virtical row" Indicies
 Dim arrOrig() As Variant                   ' This    arrIndx() = Application.Index(arrOrig(), Rs(), Cms())  applies the modified Rs() to the original unsorted data range. So we need an array to use constantly containing the original data range

Calling routine Sub Call_Sub_Bubbles()
Usually routines that take arguments at the signature line ( in the Sub( x As Long, …. start bit) ) , cannot be run directly , and must be set off by another “Calling” routine which passes the required arguments at the “Call” line. For a recursion routine this is also the case for the first copy of the routines. ( Further copies start as the existing copy pauses after itself Calling another independent copy of the recursion routine to start )
So we need a calling routine…._
Sub Call_Sub_Bubbles() Calls Sub Bubbles Copy1
Sub Bubbles Copy1 Calls Sub Bubbles Copy2
Sub Bubbles Copy2 Calls Sub Bubbles Copy3
_............
For convenience we can also, in this routine, for test purposes create the array from a worksheet range which we will pass as the array to be sorted by our test range.
This array, arrTS() , is passed to the recursion routine as one of the main arguments. ( The signature line of the recursion routine will declare the variable that needs to be passed this array, as being ByRef . This will means that all changes on that array inside the recursion routine , ( and necessarily any single copy of the recursion routine running at any time ) are referred back to this array. Effectively this can be thought of as the array arrTS() as being “carried in” the recursion routine signature line defined variable, and similarly one can consider all done on the array as being done on this arrTS()
The next characteristic of this Calling routine was in this Thread so far only used for when a recursion routine was called using the index way idea ( __Sort8 , __Sort7 codings )
I can’t easily replace in all codings the string of row indices , “ 1 3 2 5 6 7 “ , with the array of row indices,
Rs() = 1;
____¬___3;
____¬___2;
____¬___5
____¬___6
____¬___7 }
For the first copy of the recursion routine , ( the only copy Called by this Calling routine ) , those will be the initial ordered rows, in our example,
{1;
2;
3
4
5
6}
But subsequently, I will likely only need a subset of the rows to further sort. It would get very messy to be chopping and changing in particular, the dimensions, of such an array. Mostly this is because it is a 2 dimensional ( all be it 1 “width”/column) array, and they are not so easy to manipulate as are 1 dimensional array that lend themselves to easy manipulation with string functions. The Join and Split are in particular very useful, but these only work on 1 dimensional arrays

So I restrict the use of the Rs() in this coding to an alternative for generating the first string values. But that is not particularly any better than the previous way..

Sub Call_Sub_Bubbles()
For convenience we use spreadsheet functions to give us sequential indices initially in Rs() , ( and later in next routines Cms() ) Evaluate(Row(1to15)).JPG : https://imgur.com/UVTQCYO ( Evaluate(Column(1to6)).JPG : https://imgur.com/jbaZdgJ )
( The, "Vertical row" Indicies, ( and the "Horizointal Column" Indicies ) , can be Long or String types, and out App.Index coode line would work, but because we use for convenience spreadsheet functions vial the VBA Evaluate(“ “) function to obtain these. As the Evaluate(“ “) returns all things housed in Variant type, we declared Rws() ( and Cms() ) , as Variant )
The information we need for the column by which to be sorted is taken in a simple string, which we organise to have a similar form to that for the Range.Sort method. For our example it looks like this: " 1 Asc 3 Asc 2 Asc " . For our simplified example , the Asc has no effect, but is included to aid in comparison with previous / further developed routines which have / will allow the option to chose Ascending or Descending sort order
For any recursion routine it is almost always useful to have a variable set at the start of any copy of the routine which tells us which copy number is running. This I typically call CpyNo, CopyNumber , CopyNo or similar.
At the start of every copy of the recursion routine, the value passed at the signature line is put in a local copy variable which can be then used to give the copy number of the current running routine.
So from the Calling routine I pass 1. At the code line in the recursion routine which Calls into life a new copy of the routine, we pass like CopyNumber + 1 . This ensures we then have the correct value place at the start of any newly starting copy of the recursion routine, since the first copy of the recursion routine pauses and starts the second copy of the recursion routine, the second copy of the recursion routine pauses and starts the third copy of the recursion routine ….. etc…

Here is our final Calling routine, Sub Call_Sub_Bubbles()
( It gives the same demo range output as Sub RangeSort() , ( or should do once I have written the routine that it Calls ) )

'
Sub Call_Sub_Bubbles() ' Partially hard coded for ease of explanation
' data range info
Dim WsS As Worksheet: Set WsS = ThisWorkbook.Worksheets("Sorting")
Dim RngToSort As Range: Set RngToSort = WsS.Range("B11:E16")
                                           ' Set RngToSort = Selection '                          ' Selection.JPG : https://imgur.com/HnCdBt8

Dim arrTS() As Variant ' array to be referred to in all recursion routines, initially the original data range
 Let arrTS() = RngToSort.Value
' Initial row indicies
 Let Rs() = Evaluate("=Row(1:6)") '
Dim strRows As String, Cnt As Long: Let strRows = " "
    For Cnt = 1 To 6
     Let strRows = strRows & Rs(Cnt, 1) & " "
    Next Cnt
' we should have now strRows = " 1 2 3 4 5 6 "
 Call Bubbles(1, arrTS(), strRows, " 1 Asc 3 Asc 2 Asc ")

' Demo output
 Let WsS.Range("B31").Resize(RngToSort.Rows.Count, RngToSort.Columns.Count).Value = arrTS()
End Sub
'

The next post develops the Called routine,
Sub Bubbles(ByVal CpyNo As Long, ByRef arsRef() As Variant, ByVal strKeys As String)