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

[DocAElstein]

Final conversion of Sub Bubbles to Sub BubblesIndexIdeaWay

As we have the current stand of the routines, as discussed in the last post, we have the final Calling routine, ( Sub Call_Sub_BubblesIndexIdeaWay(), http://www.excelfox.com/forum/showth...ll=1#post11074 )
But currently , Sub BubblesIndexIdeaWay , is basically, Sub Bubbles
This Final conversion of Sub Bubbles to Sub BubblesIndexIdeaWay will be done in twp parts: Firstly we will get the same results in arrIndx() as we already have in arsRef(). Then we will remove the redundant bits

Part 1 Additions to make arrIndx() give us final Output
From the last post, we see that we do not yet have the reordered Rs() at the end of the routines running. Therefore this will give the wrong final results
____________arrIndx() = Application.Index(arrOrig(), Rs(), Cms())
Getting the correct Rs() is the main requirement to get Sub Bubbles working

We need to remind ourselves what goes on in basic the sort, after which it is fairly obvious how we get the reordered indices:
In the bubble sort, we reach a point whereby rows need to be swapped. Lets consider as random example that rows 2 and 3 need to be swapped, so
row 2 become row 3 , and row 3 becomes row 2.
As discussed we cannot do that easily in computing. In computing we need to have a temporary variable, pseudo
Temp = row 2
row 2 = row 3
row 3 = Temp

Our variable Rs() , holds the indices we need to apply to the original array to get the new reordered array. The current coding swaps rows. Rs() holds the indices of our original array. If we want the indicies in that Rs() , when applied to the original array, to return the reordered array, then they need to be swapped at exactly as the rows currently in arsRef() are.
Putting that again in just slightly different wording. The original rows have an original row indicie. As the rows are moved ( swapped) up and down in arsRef() , the corresponding row indicie needs to be moved excactly the same in Rs()
I can’t put that any clearer. You need to take a bit of time to let that run through your head, and then it should make sense.

Once we have understood that last bit, then the coding modification may be obvious: At the current swap section we need to swap the indicies in Rs()
Currently we have this:

            Dim Clms As Long '-------| with the condition met  a loop is done for all columns in the array in which those two values used in the comparison are replaced at each column
                For Clms = 1 To UBound(arsRef(), 2)
                 Let Temp = arsRef(rOuter, Clms): Let arsRef(rOuter, Clms) = arsRef(rInner, Clms): Let arsRef(rInner, Clms) = Temp
                Next Clms '----------| for each column in the array at the two rows rOuter and rInner

We simply add there a swap for the idiocies

            Dim Clms As Long '-------| with the condition met  a loop is done for all columns in the array in which those two values used in the comparison are replaced at each column
                For Clms = 1 To UBound(arsRef(), 2)
                 Let Temp = arsRef(rOuter, Clms): Let arsRef(rOuter, Clms) = arsRef(rInner, Clms): Let arsRef(rInner, Clms) = Temp
                Next Clms '----------| for each column in the array at the two rows rOuter and rInner
            Dim TempRs As Long
             Let TempRs = Rs(rOuter, 1): Let Rs(rOuter, 1) = Rs(rInner, 1): Let Rs(rInner, 1) = TempRs

If you make just that modification and run the complete coding, ( see here for example http://www.excelfox.com/forum/showth...ll=1#post11076 ) , then you will find that the final indicies are correct…. Compare the input:

	1	2	3	4
1	1	5	3	a
2	9	9	9	b
3	1	4	2	c
4	8	8	8	d
5	1	3	2	e
6	7	7	7	f

Here the output

	1	2	3	4
5	1	3	2	e
3	1	4	2	c
1	1	5	3	a
6	7	7	7	f
2	9	9	9	b
4	8	8	8	d

We have the required Rs() , and can add the code line to apply to the formula , arrIndx() = Application.Index(arrOrig(), Rs(), Cms())
We could add this code line now anywhere, for example at the end of the calling routine

 Let RngDemoOutput = arrIndx()
 Let RngDemoOutput.Offset(-1, 0).Resize(1, 4).Value = Cms(): RngToSort.Offset(-1, 0).Resize(1, 4).Font.Color = vbRed
 Let RngDemoOutput.Offset(0, -1).Resize(6, 1).Value = Rs(): RngToSort.Offset(0, -1).Resize(6, 1).Font.Color = vbRed
'
 Let RngDemoOutput.Offset(RngDemoOutput.Rows.Count, 0) = Application.Index(arrOrig(), Rs(), Cms())
End Sub

The above coding modification would give us this output:

Row\Col	A	B	C	D	E
30		1	2	3	4
31	5	1	3	2	e
32	3	1	4	2	c
33	1	1	5	3	a
34	6	7	7	7	f
35	2	9	9	9	b
36	4	8	8	8	d
37		1	3	2	e
38		1	4	2	c
39		1	5	3	a
40		7	7	7	f
41		9	9	9	b
42		8	8	8	d

The above use of the formula, Application.Index(arrOrig(), Rs(), Cms()) , is helpful to demo its use.
However, before we can move on in the next post to removal of redundant things, that is to say, code actions, we must do some further modifications.

_.___________________

The next two modifications are not immediate obvious at this stage, but it should become clearer why we need to do this, once the removing of redundant parts and associated modifications are discussed in the next post.

Rem 2
The modification so far will not be enough once we remove the redundant parts, since we must pass the modified full arrays at the Call code lines within the recursion routine. We will therefore need to have available the modified array, as given by arrIndx() , after the sort , Rem 1, and before the recursion call section, Rem 3

    Next rOuter ' =============End Rem 1=================================================================
Rem 2
 Let arrIndx() = Application.Index(arrOrig(), Rs(), Cms())
Rem 3 Preparation for possible recursion Call

_._____________
As final modification in preparation of the removal of redundant parts, we need to use and send the arrIndx() rather than arsRef()

_._____________
As mentioned, these last two modifications are not immediate obvious at this stage, but it should become clearer why we need to do this, once the removing of redundant parts and associated modifications are discussed in the next post.
_.___________
Here are the final modifications before removal of redundant parts….
http://www.excelfox.com/forum/showth...ll=1#post11077

The next post considers modifications to remove redundant code actions