VBA Range.Sort with arrays. Alternative for simple use.
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 |
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).
Code:
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 |
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 |
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 5th 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 |
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 |
Minor Modifications advancements to simple Bubble Sort Routine
Minor Modifications advancements to simple Bubble Sort Routine
Change Ascending/Descending
For the simple example so far we chose Ascending order, which means that the things "get bigger as you go down the rows"
To reverse this to descending so that things "get smaller as you go down the rows", you simply need to change
the > to a < in the array routine
and
the Order1:=xlAscending to Order1:=xlDescending in the VBA Range.Sort routine
http://www.excelfox.com/forum/showth...0979#post10979
I intended developing the solution into a function, so as a start to this, the routine will be modified to take an Optional argument of 0 or 1 , with the default of 0 being the case for an Ascending list. I am not being particularly efficient with the coding, and will duplicate sections thus
Code:
Rem 1 Simple Bubble Sort
Dim rOuter As Long ' ========"Left Hand"=====================Outer Loop=====================================
For rOuter = 1 To UBound(arrTS(), 1) - 1 ' For row 1 to the (last row -1) last row, given by the first dimension upper limit of the array
Dim rInner As Long ' -------Inner Loop-------------"Right Hand"--------------------------
For rInner = rOuter + 1 To UBound(arrOut(), 1)
'If arrOut(rOuter, Clm) > arrOut(rInner, Clm) Then ' This means that I am bigger than the next. So I will swap . I keep doing this which will have the effect of putting the smallest in the current rOuter. By the this and all next rOuter, I miss out the last, and any previous, which means I effectively do the same which puts the next smallest in this next rOuter.
If GlLl = 0 Then ' We want Ascending list
'If UCase(CStr(arrOut(rOuter, Clm))) > UCase(CStr(arrOut(rInner, Clm))) Then
If UCase(CStr(arrOut(rOuter, Clm))) > UCase(CStr(arrOut(rInner, Clm))) Then
Dim temp As Variant ' I want to Swap those 2 above - I cant easilly in any coding change two values simulataneosly. So one of them Element values will put in this temporary place. This Element Values will then be given the other. Finally the other Element will be given this temporary value
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(arrOut(), 2)
Let temp = arrOut(rOuter, Clms): Let arrOut(rOuter, Clms) = arrOut(rInner, Clms): Let arrOut(rInner, Clms) = temp
Next Clms '----------| for each column in the array at the two rows rOuter and rInner
Else
End If
Else ' GlLl is not 0 , so presumably we want Descending list
If UCase(CStr(arrOut(rOuter, Clm))) < UCase(CStr(arrOut(rInner, Clm))) Then
'Dim temp As Variant ' I want to Swap those 2 above - I cant easilly in any coding change two values simulataneosly. So one of them Element values will put in this temporary place. This Element Values will then be given the other. Finally the other Element will be given this temporary value
'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(arrOut(), 2)
Let temp = arrOut(rOuter, Clms): Let arrOut(rOuter, Clms) = arrOut(rInner, Clms): Let arrOut(rInner, Clms) = temp
Next Clms '----------| for each column in the array at the two rows rOuter and rInner
Else
End If
End If ' End of Ascending or Descending example
Next rInner ' ---------------------------------------------------------------------
Next rOuter ' ===========================================================================================
Rem 2 Output for easy of demo
Here is a complete routine with calling procedure: http://www.excelfox.com/forum/showth...0981#post10981
Text or Numbers
It appears from the example tried, that the VBA Range.Sort routine recognizes numbers as numbers and then sorts as we might expect numbers to be sorted.
We can modify slightly our array routine to give similar results.
This is done by doing a comparison based on looking at numbers if at any time the two values under consideration can be seen by VBA as numbers.
The IsNumeric() function is used. If both values are seen to be numeric then they are compared as numbers. This would probably happen from a simple comparison of the values, but just to be on the safe side, we do an extra convert to Double via CDbl( ) in this sort of form
___ If IsNumeric(arrOut(rOuter, Clm)) And IsNumeric(arrOut(rInner, Clm)) Then ' Numeric case
_____ If CDbl(arrOut(rOuter, Clm)) > CDbl(arrOut(rInner, Clm)) Then
Once again, I am not coding particularly efficiently , and so I duplicate sections
Here is a complete routine with calling procedure: http://www.excelfox.com/forum/showth...0982#post10982
Here are some example results: http://www.excelfox.com/forum/showth...0983#post10983
The next post will make a few minor simplifications to develop further a general purpose Function.
Conversion to Array sort function: General considerations
Conversion to Array sort function
From now on, I am making up a lot of this as I go along, so I doubt it will bee the most efficient.
General considerations.
_ Always reference the same array
My long term plans on the final version of this is that the routine will work in some recursion process, that is to say , copies of the function will be going further and further to sort further for rows where the last sort left some duplicated values in the column used to sort by. Those copies of the Function would be called from the function itself. That is the simple explanation of hat recursion is about , as applied to VBA Functions: the function starts, then it pauses, whilst it set off another run of the Function to go off and do something similar to what the first function was doing.
One thing that is always a bit awkward to do is pass the array being sorted through the different copies of the function. So what is normally done, and what we will do is have an array outside the function, ( either in an initial calling routine or at the top of the code module )
In the signature line , ( declaring first line) , of the Function it will be defined as being taken ByRef This means that within the Function we refer specifically to that array. This means that changes made in that Function will be reflected directly by those changes into the array we have outside: To a first approximation we can think of it as thought we physically take the array into the function and do stuff to that array, then put it back outside when we have finished. ( The alternative way that we will not use would be to take the array into the function , ByVal , as its name suggests, takes the values of the array at the time into a copy variable , ( sometimes referred to as a local copy variable ), and all is done inside the function on that copy variable , which ceases to exist when the function Ends. The values inside the original array outside are not effected in the ByVal case.
Simple pseudo code examples of difference between ByRef and ByVal:
Pseudo code ByRef ' ( Usually default option )
varMyArray = x
_ Call ReferToIt(varMyArray)
Sub ReferToIt(ByRef arr)
_ arr=y ' This is similar to saying varMyArray = y
End
varMyArray is now = y ' because effectively varMyArray was in arr
Pseudo code ByVal
varMyArray = x
_ Call TakeValue(varMyArray)
Sub TakeValue(ByVal arr)
_ arr=z
End
varMyArray is still = x ' effectively just a copy of varMyArray was in arr , and this copy was thrown away at the End of the routine. So nothing done to arr could have any effect on varMyArray in the ByVal case
Sort only consecutive rows
I am anticipating wanting to sort a number of consecutive rows in the array. On the first run this will be all rows, like for a 5 row array _ 1 2 3 4 5 if the
If the sort based on the values in an initial column found that the third and forth row had a similar value in that column, then a sort based on another column would need in comparison 3 4.
So I am suggesting a strategy could be to have the function do the sort routine based on consecutive rows given in some way, such as in a text string.
The next posts will look at implementations of the general ideas above
Transformation to a Function or Pseudo Function Sub routine
Transformation to a Function or Pseudo Function Sub routine
The transformation to a Function we can do easily in two simple steps.
Step 1 The routine is changed so that it takes the array to be sorted into it
The calling routine and main routine here http://www.excelfox.com/forum/showth...0987#post10987 are almost the same as the previous. The only difference is that the array to be sorted, is now outside the main routine and is taken into the main routine at the main routine signature line
Sub SimpleArraySort4(ByRef arrTS() As Variant, Optional ByVal GlLl As Long)
( A few unused 'commented out lines have also been removed )
Because we are using ByRef , the previous testieing Calling routine can also use the original supplied array, arrTS() , after the main procedure Call , provided that the array taken in at the signature line is that sorted. If this is the case, then , in effect , the array taken in and the array in the routine are the same array, and changes done to the array in the function will be reflected in the array outside.
We discussed this previously. Just to recap:
Pseudo code ByRef ' ( Usually default option )
varMyArray = x
_ Call ReferToIt(varMyArray)
Sub ReferToIt(ByRef arr)
_ arr=y ' This is similar to saying varMyArray = y
End
varMyArray is now = y ' because effectively varMyArray was in arr
Often in learn material the basic difference between a simple Function procedure and a Sub routine procedure is said to be that a Function returns a value. In fact , as we see from above, the use of ByRef actually allows us effectively to return a value from a simple Sub procedure.
In the version Sub TestieSimpleArraySort4b() and Sub SimpleArraySort4b(__) here , http://www.excelfox.com/forum/showth...0988#post10988 we are using the effect of ByRef to give us a pseudo Function : To achieve this pseudo Function status, all the array references within the routine are changed to that variable taken into the routine at the signature line.
( This variable name can be anything, I will use arsRef() from now on for all references in the main routine). Doing this allows us to remove all the demo range code lines from the procedure: They can be placed outside and then we can use the array which we declare initially outside the routine, ( arrTS() ) , both before and after the procedure Call: Before it is used to feed the data to be sorted, and After the Call it had been filled with the sorted results , so can be used to paste out the sorted results alongside the original range to compare for demo purposes. To all intents and purposes, arrTS() can be considered as arsRef() and visa versa. This characteristic has been achieved by the use ByRef: Any use of arsRef() is simultaneously Referring to
Step 2 Function instead of Sub ???
I am doing this for completeness. But because I am doing things ByRef I don't need to do this. I may not use it finally
Often in learn material the basic difference between a simple Function procedure and a Sub routine procedure is said to be that a Function returns a value.
This can be explained as follows.
The signature line differs from a normal Sub routine in that it has the form similar to a Dim declaration code line. Consider such a simple Dim line:
_ Dim __ MyThing __ As Variant
A signature line for a Function could look like
Function MyThing(Arg) _ As Variant
In both cases you effectively have a variable of the specified type. The way you fill the variable is slightly different. In the simple case you fill it with something
__ MyThing = "42"
For the function you need to Call it to fill it, and unless the Argument is optional, then you must give it
_ MyThing_ "42"
or
_ Call MyThing("42")
Because the function usually ( you can decide, it does not have to !!!!! ) returns a value in a variable of the type you specify , the most typical use would look like
MyOtherThing = MyThing("42")
The returned value is then put in the variable MyOtherThing
At the end of the day, the main reason for using a function is given as that of returning a value of a specific type.
( By the way, once you have such coding written in a File , then in addition to using it in VBA,,then you could sometimes use it in a cell: Writing = MyThing("42") would often work to return you a value in the cell , resulting from what your function MyThing did with "42" )
If we declared our function as type Variant at the signature line, then that would allow us to return our sorted array, since a Variant variable can hold an array.
The reason why I may not finally use this is that by virtue of using ByRef, my Sub routine will also effectively return that array.
Within the Function itself, the Function MyThing, is like an unfilled variable , in this case a Variant variable. As it is unfilled it will not return anything. !!!!! So within the function, typically towards the end once we have finished doing all we wanted to do, we would have a code line like
Function MyThing(Byref ArrToSort() , ByVal aArg, Optional ByVal GlLl As Long) As Variant
' arrOut() = ArrToSort()
' Do stuff like sort the array
_ MyThing = arrOut()
End Function
The function is then "filled" with what it would return when called
Transformation of pseudo Function Sub SimpleArraySort4b(ByRef arsRef() As Variant, Optional ByVal GlLl As Long) to Function SimpleArraySort5(ByRef arsRef() As Variant, Optional ByVal GlLl As Long) As Variant
Let us note once more that this final step is not necessary: The last procedure fulfils all are current requirements of taking an array and sorting that array, and effectively giving it back to us. It does this by virtue of the array being taken ByRef
The true Function way is more conventionally used so I am doing it just for convenience
The convention way to use the Function would be to arrange that our sorted array is returned by the function. As our current routine stands we only need to add one code line to do this. This code line, placed towards the end of the Function would be
__SimpleArraySort5 = arsRef()
To explain:
The signature line .._
Function SimpleArraySort5(_____) As Variant
_.. Has a very similar effect to this line:
_ Dim _ SimpleArraySort5 _ As Variant
So SimpleArraySort5 could be thought of , to a first approximation , as a pseudo variable
The only difference is that when VBA ( or Excel following a = , like in = SimpleArraySort5 , in a cell ) "sees" SimpleArraySort5 , it will go off and do whatever is inside the function. At the End of the function, the value inside the pseudo variable will be empty still. If we do nothing else then the Function can be Called in exactly the same way that a normal sub routine can
_ Call SimpleArraySort5(___)
However, filling the pseudo variable , for example via code line SimpleArraySort5 = arsRef() , allows us the extra possibility of a code lines like
_____ = = SimpleArraySort5(____)
_ MySortedArray= SimpleArraySort5(MyUnsortedArray)
The type chosen in the declaring signature line must be compatible with what you are doing. In our case, we want to return an array. The only variable type that can hold/ pass an array is a Variant. Hence we chose Variant in this case
( because VBA works generally backwards when reading a code line, we could, in our case , use the same variable such
_ MyUnsortedSortedArray= SimpleArraySort5( MyUnsortedSortedArray )
_ arrTS() = SimpleArraySort5( arrTS() ) )
In the routines
Sub TestieSimpleArraySort5() and Function SimpleArraySort5(______) As Variant
http://www.excelfox.com/forum/showth...0989#post10989 , the main difference over the previous routines is the extra As Variant at the signature line, and finally a code line just before End Function of SimpleArraySort5 = arsRef()
In the testieing routine, we use codes line of this form in the conventional way in which a function is typically used.
_ arrTS() = SimpleArraySort5(arrTS(), _ 0 _ )
But we note that by virtue of using ByRef a simple call would surfice
_Call SimpleArraySort5(arrTS(), _ 0 _ )
Note: we have added an extra testing code section '2b)
In this extra section we fill a new array, arrDesc() , with the sorted array in Descending order. We use for demo purposes a typical function using code line
_ arrDesc() = SimpleArraySort5(arrTS(), 2136)
Correspondingly we have a demo output giving code line
_ RngToSort.Offset(0, RngToSort.Columns.Count * 3).Value = arrDesc()
We note further, that this is somewhat redundant. This is because the code part SimpleArraySort5(arrTS(), 2136) has the effect of re filling arrTS() with the newly sorted array by virtue of the use of ByRef in the signature line of the Function
We could therefore simply use a code line like _..
_Call SimpleArraySort5(arrTS(), 357)
_.. followed by an demo output giving line of
_ RngToSort.Offset(0, RngToSort.Columns.Count * 3).Value = arrTS()
We mow have a simple Array Bubble sort function
The next posts will develop ideas to allow extending the function to allow for sorting further using values from other columns for rows where duplicate values were in the column used initially
I will likely start from the "pseudo" Function
Sub SimpleArraySort4b(ByRef arsRef() ______)
Example: A few thousand columns with a few dozen rows
Example: A few thousand columns with a few dozen rows
A requirement I have is to re sort occasionally a sub set of rows in a large list of very wide rows ( approx 3500 columns ). Typically I have sections of 50 rows which “belong together”, ( 49grouped + 1 below ) , and things may be added. It is easier to add anywhere in the section and then do a sort on the entire group, or part thereof , as required..
49grouped+1.JPG : https://imgur.com/a8rflEn
The Range.Sort method works well, so the array version is just a spare alternative…
Range.Sort method coding.
A full description is given here:
http://www.excelfox.com/forum/showth...ll=1#post11032
http://www.excelfox.com/forum/showth...ll=1#post11033
http://www.excelfox.com/forum/showth...ll=1#post11034
http://www.excelfox.com/forum/showth...ll=1#post11035
Here is just a brief Description of the Range.Sort method coding:
The range to sort, rngToSort , is determined. It is basically taken as the user selected range on the active worksheet in the active window. Because there are a lot of columns, and the number is known ( 1 – 3488 ) , these are hardcode, and the user only needs to make a selection of any columns over the required rows: The selection determines the row range and the column range is hard coded.
An array, ArrrngOrig() , is made of the current range values.
This can be used if it is decided not to accept the sorted range and instead to replace the original range order.
In the Array version alternative this will be the array to be given to the sort routine.
The main code line doing the Range.Sort is
Code:
rngToSort.Sort Key1:=wksToSort.Columns("H"), order1:=xlDescending, Key2:=wksToSort.Columns("J"), order2:=xlDescending, Key3:=wksToSort.Columns("X"), order3:=xlDescending
The relevant arguments from there that we need to feed to our array sort routine are therefore
Key1:=wksToSort.Columns("H"), order1:=xlDescending
Key2:=wksToSort.Columns("J"), order2:=xlDescending
Key3:=wksToSort.Columns("X"), order3:=xlDescending
The array to be given can be assigned directly to
ArrrngOrig()
Array sort routine alternative coding( revision of our current coding )
Here is our Main recursion Array sort routine http://www.excelfox.com/forum/showth...0994#post10994
Sub SimpleArraySort6(ByVal CpyNo As Long, ByRef arsRef() As Variant, ByVal strRws As String, ByVal strKeys As String)
Because this is a recursion routine, it need an initial routine to Call it the first time and feed it the appropriate arguments.
It will need to supply :
CpyNo : This is is very useful for debugging to help tell us which copy of the routine is running at any time. We have seen that this is incremented every time a routine pauses and sets off another copy. The increment is done as the new copy is started, and that incremented value passed to the newly starting routine, so CpyNo will be an indication of the copy running. So this we will need to set to one at the initial Call done by the initial Calling routine to 1
Also it is needed to pick out the correct sort key, in other words to set the copy of the routine to sort based on the correct column: Each further copy of the recursion routine uses the next column of values to be used in the progressive sorting.
arsRef() : We can declare a dynamic Variant array and assign our range capture array , ArrrngOrig() , directly to this. Lets call this arrTS() for consistence to our previous discussions
strRws : This is used to give the indices of the array, ( in a string form ) of all rows currently being sorted. On the initial Call this will need to have all the rows and be of this form “ 1 2 3 4 5 6 ……….. N “. We will need to make this in the Calling routine
strKeys : This takes a form to look similar to the arguments of the Range.Sort method. Because we are looking from column A , our columns follow the spreadsheet columns. All our order is Descending. This would be our basic form required, which will be passed unchanged between copies of the recursion routine. ( We pass this ByValue , but we could just as well uses ByRef for this variable )
In our initial Calling routine we will need a code line of this form
strKeys = “ 8 Desc 10 Desc 24 Desc “
Calling initial routine
The routine is here http://www.excelfox.com/forum/showth...ll=1#post11038
The first 3 section, Rem 0 Rem 1 Rem 2 , and final section Rem 4 are not directly relevant to the sort routine, and are very similar to the same code sections in the Range.Sort routine described here: http://www.excelfox.com/forum/showth...ll=1#post11035
Rem 3
'3a) arguments for Called routine
A simple loop produces our string of all row indices, strIndcs
An array is made containing the initial unsorted range to be passed to the recursion routine, arrTS()
The remaining arguments of recursion routine copy number, CpyNo , abd the keys required, strKeys , are passed hard coded directly in the next section, '3b)
'3b) ( '3c) alternative**) This is the main equivalent to the Range.Sort method
The main recursion routine is Called and after the range is given the array of sorted values
**‘3c) is an alternative which comes closer to resembling the single line Range.Sort method , for comparison:
Code:
rngToSort.Sort Key1:=wksToSort.Columns("H"), order1:=xlDescending, Key2:=wksToSort.Columns("J"), order2:=xlDescending, Key3:=wksToSort.Columns("X"), order3:=xlDescending
'3c)
arrTS() = rngToSort.Value: Call SimpleArraySort6(1, arrTS(), strIndcs, " 8 Desc 10 Desc 24 Desc "): rngToSort.Value = arrTS()
here again, pseudo form, for ease of comparison:
rngToSort.Sort
___________Key1:=wksToSort.Columns("H"), order1:=xlDescending,
_________________Key2:=wksToSort.Columns("J"), order2:=xlDescending,
_______________________Key3:=wksToSort.Columns("X"), order3:=xlDescending
‘ 3c)
arrTS() = rngToSort.Value
Call SimpleArraySort6(1, arrTS(), strIndcs,
__________________________" 8 Desc 10 Desc 24 Desc ")
rngToSort.Value = arrTS()
( For a spreadsheet, for the letter across the top,
_____________H is column number 8 , J is column number 10 , X is column number 24 )
Here is the file used do far:
"ProAktuellex8600x2Sort1.xlsm" https://app.box.com/s/d6isabudadt3swnryxiz7motspzeqa17
Here are some timing experimants:
http://www.excelfox.com/forum/showth...ll=1#post11041
http://www.excelfox.com/forum/showth...ll=1#post11042
http://www.excelfox.com/forum/showth...ll=1#post11043
http://www.excelfox.com/forum/showth...ll=1#post11045
http://www.excelfox.com/forum/showth...1046#post11046
Alternative Version. Sort row indicies, then use arrOut()=Indx(ArrOrig(). Rs(), Cms() )
I am thinking of an alternative approach, the idea being to reduce on the steps to reorganising the array at every swap stage.. the idea came from 2 things …
_1 The recursion routine is fed currently the row indices of the rows that need to be sorted.
_2 We can use the VBA Application.Index Method which allows us to re sort an array “ in one go “ via a code line like , pseudo formula..
arrOut() = App.Indx( ArrIn() , {1;3;2} , {1,2,3} )
arrOut() = App.Indx( ArrIn() , row indices , column indicies )
arrOut() = App.Indx( ArrIn() , Rs() , Cms() )
The above code line would change an ArrIn() like this …_
A b c
D e f
G h I
_ … to a given output in arrOut() like this:
A b c
G h I
D e f
So the idea is that we sort the indices values, and then re apply the formula above
At this stage I propose modifying the existing code so as to have a better chance of a direct comparison in performance.. Both will be then subject to similar general inefficiencies arising from the very opened out explicit form of the codlings generally. I will do two version of this code, Sub SimpleArraySort7( and Sub SimpleArraySort8(
Sub SimpleArraySort7( will add the extra coding, and Sub SimpleArraySort8( will remove some of the now unnecessary / redundant parts thereafter, so as to attempt a good comparison to the previous Sub SimpleArraySort6(
Finally I may make a more trimmed version
Here the basic modifications for Sub SimpleArraySort7(__ , Sub TestieSimpleArraySort7()
Global Variables
To help simplify the comparison and so reduce the changes to the routines, I will have a some Global variables at the top of the module and outside any routine for the row and column indices
Dim Cms() As Variant, Rs() As Variant
This will allow me to refer to, that is to say change and use, in any copy of the recursion routine. (It would also be an alternative place here at the top of the module and outside any routine for our main array, arrTS(): we could then always refer to this, and then not need the ByRef arsRef() at the signature line of the recursion routine. But for now I will leave that as it is for closer comparison of the routines. )
These two “single width” arrays,
_ Rs() , “vertical, rows”
and
_ Cms() , “horizontal columns”
, will hold whole number indices for use in the VBA Index method formula idea.
For the type of the elements of these arrays, the Long type would be OK, and also in such situations VBA usually accepts Sting types that look like an number. The only reason that I use Variant is that I use a convenient way to get the initial indices, and that way happens to return a field of Variant types
To help in the development of this coding and to help with the explanation here, I have also moved the variable for the test range, RngToSort , to the top of the module and outside any routine to make it a global variable: This way I can use multiples of it’s dimensions to position intermediate paste outs of the arrTS(). For example , I have added a section immediately after the end of the main outer loop == for sorting, ' Captains Blog, Start Treck , which pastes out the current state of the sorted array , arrTS(), along with the current state of the indices, Rs()
Code:
' Captains Blog, Start Treck
Let RngToSort.Offset((RngToSort.Rows.Count * (CopyNo + 1)), 0).Value = arsRef()
RngToSort.Offset((RngToSort.Rows.Count * (CopyNo + 1)), -1).Resize(UBound(Rs(), 1), UBound(Rs(), 2)).Value = Rs()
Debug.Print " Running Copy " & CopyNo & " of routine." & vbCr & vbLf & " Sorted rows " & strRws & " based on values in column " & Clm & vbCr & vbLf & " Checking now for Dups in that last sorted list" & vbCr & vbLf
For Sub SimpleArraySort7(__ , I will also include a new array variable , as a global variable, arrIndx(). This I will fill by the formula line of
arrIndx() = Application.Index(arrOrig(), Rs(), Cms())
The formula above needs to be applied to the original range, so I also have another global variable which will contain the original range, arrOrig()
The final output, for example from the first passing of this section, can be seen here:
http://www.excelfox.com/forum/showth...ll=1#post11049
In that screenshot the output from the previous routines produced in
__arsRef()
is shown and , for comparison, alongside it is shown that produced by
______________arrIndx() = Application.Index(arrIndx(), Rs(), Cms())
Modifying indices values in main sort loop sorting
In other words, how do we get the modified Rs() to use in
arrIndx() = Application.Index(arrIndx(), Rs(), Cms())
The way the current coding is organised makes this fairly simple. We have sections where all column elements in a row are swapped.
__ 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
We use the row information in the variables rOuter and rInner. So quite simply, we do the same swap for row indices,
Dim TempRs As Long
_ TempRs = Rs(rOuter, 1): Let Rs(rOuter, 1) = Rs(rInner, 1): Let Rs(rInner, 1) = TempRs
With those simple modifications we obtain for the final full run the results shown here:
http://www.excelfox.com/forum/showth...ll=1#post11050
Summary
In the next post the actual modifications to transform Sub SimpleArraySort6( to Sub SimpleArraySort7( are walked through in a bit more detail. Here is the brief summary to help in orienteering.
In the previous recursion routines, ( and still in this intermediate version, Sub SimpleArraySort7( ) the entire row values are swapped at various stags in the Bubble sort process. In our codings so far, we had typical sections like this
Code:
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
In that above snippet, every column value in a row goes through the typical three line swap using a temporary variable: __ temp=My1 _ My1=My2 _ My2=temp https://excel.tips.net/T002525_Swapp...o_Numbers.html
For the new coding idea, we only need to swap the row indices so as to change their order from like {1;2;3;4….} to the new sorted order like ModifiedRowIndicies ={2;1;4;3….}, since then we will apply the idea .._
__ arrNext() = App.Indx( originalRange , rows() , columns() )
__ arrNext() = App.Indx(originalRange, ModifiedRowIndicies , {1,2,3,4,…..})
_.. to get the modified row order from the original range. ( The columns, columns() , remain in the original order , {1,2,3,4,…..} )
So we have an extra code line which just swaps the row indicia. ( In this intermediate version I still include the previous swap sections )
Code:
Dim Temp As Variant ' I want to Swap those 2 above - I cant easilly in any coding change two values simulataneosly. So one of them Element values will put in this temporary place. This Element Values will then be given the other. Finally the other Element will be given this temporary value
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
The main other extra code section is then the code line to get the new row order using the reordered row indices
___ arrIndx() = Application.Index(arrOrig(), Rs(), Cms() )
This done at the end of the sort section
Code:
Next rInner ' ---------------------------------------------------------------------
Next rOuter ' ===========================================================================================
Debug.Print "Doing an arrIndx()"
Let arrIndx() = Application.Index(arrOrig(), Rs(), Cms())
' Captains Blog, Start Treck
1 Attachment(s)
Sub TestieSimpleArraySort7() Sub SimpleArraySort7( )
Sub TestieSimpleArraySort7()
Sub SimpleArraySort7(ByVal CpyNo As Long, ByRef arsRef() As Variant, ByVal strRws As String, ByVal strKeys As String)
Global variables
At the top of the module in which are codes are we need our global variables
Code:
Dim Cms() As Variant, Rs() As Variant ' "HorizointalColumn" Indicies , "Virtical row" Indicies
Dim RngToSort As Range ' Test data range
Dim arrIndx() As Variant ' For modified array at end of each sort of a set of rows
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
Sub TestieSimpleArraySort7()
I use the same basic test data range as in other routines in the last few versions of Sub SimpleArraySort7(
Additionally I need the original data range in the separate ( global) array, arrOrig() , to use continuously in the first argument in the code line,
______Application.Index(arrOrig(), Rs(), Cms())
So this takes the value of the test data range
My array of column indicies , Cms() , is conveniently obtained in one go using a spreadsheet function , Column( ) which returns the column numbers of a given range in a horizontal array. I want like {1, 2, 3, 4, 5}, for my 5 column range, so I use something like Column(A:E) , Evaluate(Column(1to6)).JPG : https://imgur.com/jbaZdgJ
Similarly I can obtain the initial required vertical array indicia {1; 2; 3; ………} from a spreadsheet function, Row( ) Evaluate(Row(1to15)).JPG : https://imgur.com/UVTQCYO
' test index
I have a short test section whereby I paste out the row and column indices as well as the original array using these indices, .._
RngToSort.Offset(RngToSort.Rows.Count, 0).Value = Application.Index(arrTS(), Rs(), Cms())
_... which should return the original range for simple ordered indices, ( https://www.excelforum.com/excel-new...ml#post4571172 )
For convenience I paste out the array thus produced directly under the original range for comparison. We find that they are identical , as expected.
Sub SimpleArraySort7(ByVal CpyNo As Long, ByRef arsRef() As Variant, ByVal strRws As String, ByVal strKeys As String)
For this version we have kept the same coding as for Sub SimpleArraySort7(_
The signature line remains the same, as all the extra variable we need are the Global variables discussed.
In every swap section we add the row indicie swap line ( 3 lines )
TempRs = Rs(rOuter, 1): Rs(rOuter, 1) = Rs(rInner, 1): Rs(rInner, 1) = TempRs
The only extra line needed for the routine to function with the Index method idea is
__arrIndx() = Application.Index(arrOrig(), Rs(), Cms())
This last line is added at just after every sort. We also include in this version a few lines to paste out the stages in the sorting for both the array from the previous method, arsRef() , and that from the Index method idea, arrIndx()
Code:
Next rInner ' ---------------------------------------------------------------------
Next rOuter ' ===========================================================================================
Debug.Print "Doing an arrIndx()"
Let arrIndx() = Application.Index(arrOrig(), Rs(), Cms())
' Captains Blog, Start Treck
RngToSort.Offset((RngToSort.Rows.Count * (CopyNo + 1)), 0).Clear ' Area for array produced from previous method
Let RngToSort.Offset((RngToSort.Rows.Count * (CopyNo + 1)), 0).Value = arsRef()
RngToSort.Offset((RngToSort.Rows.Count * (CopyNo + 1)), RngToSort.Columns.Count).Clear ' Area for array produced by Index method idea
Let RngToSort.Offset((RngToSort.Rows.Count * (CopyNo + 1)), RngToSort.Columns.Count).Value = arrIndx()
RngToSort.Offset((RngToSort.Rows.Count * (CopyNo + 1)), -1).Resize(UBound(Rs(), 1), UBound(Rs(), 2)).Clear
Let RngToSort.Offset((RngToSort.Rows.Count * (CopyNo + 1)), -1).Resize(UBound(Rs(), 1), UBound(Rs(), 2)).Value = Rs() ' Current indicies order to apply to original range
Debug.Print " Running Copy " & CopyNo & " of routine." & vbCr & vbLf & " Sorted rows " & strRws & " based on values in column " & Clm & vbCr & vbLf & " Checking now for Dups in that last sorted list" & vbCr & vbLf
' Rem 3 Determine any duplicates in sort column values , and re run the routine to sort them by another column
Coding here
http://www.excelfox.com/forum/showth...ll=1#post11053
http://www.excelfox.com/forum/showth...ll=1#post11052
and in attached File
In the next post , the code is simplified slightly to just do the Index method idea
Taking SimpleArraySort7 to SimpleArraySort8
Here are the key simplifications to take SimpleArraySort7 to SimpleArraySort8
Array referencing
Version Sub SimpleArraySort8(_ has as main distinguishing characteristic the removal of , or rather replacement of array , arsRef() , by that used in The Index method code line, arrIndx() . This array, arrIndx() , is moved from global variables: The array taken for By Referencing at the signature line will be arrIndx() in place of arsRef() . All references to arsRef() are replaced by arrIndx()
This could have been left as a global variable, but by declaring it in the signature line of Sub SimpleArraySort8(_ makes it the “housing carrying” wrapper for the supplied variable to the recursion routine array , and we can then leave the global variable with that same name arrIndx() for further use in the previous Sub SimpleArraySort7(_
The arrays for the index line in the recursion routine arrOrig() , Rs() , Cms() , remain as globals.
We will use the arrTS() from the original range capture to pass to be referenced to ( housed effectively within arrIndx() ). So that will be our final sorted array. ( arrIndx() will also be our final sorted array up to the point of the finally Ending of the first copy of the recursion routine, at which point that will then die: It is effective isolated from the “outside code module world”, so does not influence the global variable with the same name which we still have and which is available still for the previous version Sub SimpleArraySort7(
Column elements in a row swapping
One of the main distinguishing characteristics of the Index idea way, is that we sort the row indices in to a new order, and then apply the code line, .._
arrIndx() = App.Ind(arrOrig() , rowindicis, columnindicies
_.. to get the new order in one go.
However we must be careful. The immediate conclusion might possibly be that all the sections swapping all column elements in a row are now redundant and so can be removed. The is almost true, but not quite: The reordering of the row indicia is following directly the bubbling through sort of the column being used in the current sort. We must therefore continue to sort/ swap this column element for the rows in parallel to sorting/ swapping the row indicie.
Thus sections such as this in the previous Sub SimpleArraySort7(_ were swapping all the column elements and row indicies for two rows determined by the row number variables rOuter and rInner, …_
If IsNumeric(arsRef(rOuter, Clm)) And IsNumeric(arsRef(rInner, Clm)) Then
__ If CDbl(arsRef(rOuter, Clm)) < CDbl(arsRef(rInner, Clm)) Then
____ 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
___ Let TempRs = Rs(rOuter, 1): Let Rs(rOuter, 1) = Rs(rInner, 1): Let Rs(rInner, 1) = TempRs
_....Those sections need to be modified now so that they just swap those two rows in the column currently used to base the sort on, ( as well as still doing the swap of the row indicia )
We note that Clms was the variable for all columns in the loop for all columns in the swapping in the code snippet above , and Clm was the variable for the current column being used to determine the current sort order. So we no longer need that loop to swap all columns, - that can be removed. But if we do this removal, we must add a swap section for the Clm column …_
If IsNumeric(arrIndx(rOuter, Clm)) And IsNumeric(arrIndx(rInner, Clm)) Then
__ If CDbl(arrIndx(rOuter, Clm)) < CDbl(arrIndx(rInner, Clm)) Then
___ Let Temp = arrIndx(rOuter, Clm): Let arrIndx(rOuter, Clm) = arrIndx(rInner, Clm): Let arrIndx(rInner, Clm) = Temp
___ Let TempRs = Rs(rOuter, 1): Let Rs(rOuter, 1) = Rs(rInner, 1): Let Rs(rInner, 1) = TempRs
Simplifying ' Captains Blog, Start Treck
A number of additions were made for the previous version in order to compare the sort at various stages made by
the previous way – that in arsRef()
and
the Index idea way – that in arrIndx()
These are no longer needed and are removed to make the routine a better direct comparison with the latest routine version used in the previous l way , Sub SimpleArraySort6(_
Calling routine Sub TestieSimpleArraySort8() ( and Global variables and a required Function )
http://www.excelfox.com/forum/showth...ll=1#post11056
recursion routine Sub SimpleArraySort8(
http://www.excelfox.com/forum/showth...ll=1#post11058
I have started doing some timing measurements for the Index way idea to complement those already done ( http://www.excelfox.com/forum/showth...ll=1#post11037 Example: A few thousand columns with a few dozen rows )
The new measurements start from approximately here:
http://www.excelfox.com/forum/showth...ll=1#post11055
http://www.excelfox.com/forum/showth...ll=1#post11061
The initial measurements do not look particularly good.
In the next posts I will review and summarise this idea for a simple example as a pre stage in a slight re Write of coding Sub TestieSimpleArraySort8()
The re write is to make better use of the row indices since I can use them in place of the string of row indices which I currently pass through routines ( strRws = “ 1 2 6 9 8 ……. “ )
Index idea Summary. Sub Bubbles , and Sub BubblesIndexIdeaWay
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,
Code:
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 :) ) )
Code:
'
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)
Sub Bubbles - Rem 1 Bubble sort of Sort
Sub Bubbles( ByVal CpyNo As Long, ByRef arsRef() As Variant, ByVal strKeys As String )
Here’s the first bit…
Code:
Sub Bubbles(ByVal CpyNo As Long, ByRef arsRef() As Variant, ByVal strKeys As String)
Dim CopyNo As Long: Let CopyNo = CpyNo
Rem -1 from the supplied arguments, get all data needed in current bubble sort
Dim Keys() As String: Let Keys() = Split(Replace(Trim(strKeys), " ", " ", 1, -1, vbBinaryCompare), " ", -1, vbBinaryCompare)
Dim Clm As Long: Let Clm = CLng(Keys((CopyNo * 2) - 2))
CopyNo:
On every recursion run , that is to say, the copy brought into life by a Call form the routine itself, this will be increased by the Recursion Call by 1.
This will be a local variable indicating the level down in recursion - I increase it by 1 at every Recursion Call, that is to say each Recursion Call in this routine gets given CopyNo+1 as the first passed argument , CpyNo
___Call Bubbles(CopyNo + 1, arsRef(), ……
During a long “tunnel down”, the number at this point will keep increasing to reflect how “far down” we are. 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…
Rem -1
Keys()
It is convenient to split " 1 Asc 3 Asc 2 Asc " , which will give me 6 elements, which with a bit of maths, I can get the information I need regarding the column to be used in determining the sort order
Clm
For the simplified routine I just want the column number used for the sort. ( 1 or 3 or 2 )
As we will see, each next copy of the recursion routine is intended to use the next column to be used in determining the sort. So a simple bit of maths determines the element, and hence the contained column number to be used :
Clm = CLng(Keys((CopyNo * 2) - 2))
So for example , at the initial Call, done by the Calling routine, CpyNo is passed 1 from the supplied CopyNo
I will then have Key(0) which in a 1 dimensional array produced by the VBA Split function is the first array element, which has “1” in it in our example.
When the routine itself sets off a new copy, the next copy, CopyNo will become 2. Our formula , CLng(Keys((CopyNo * 2) - 2)) will use Key(1) which in a 1 dimensional array produced by the VBA Split function is the third array element, which has “3” in it in our example.
( I want a number, so I use Clng , but that probably is not needed, as VBA has a habit of usually accepting a string which “looks” like a number as the number it looks like, ( i.e. it takes “8” as 8 ) , in any situation where it is excepting a number )
Rem 1 Bubble Sort
I must have explained this basic idea a thousand times to myself and others. But I find I need to re say it every time to remind myself.
Consider initially the first copy run …
I have rows of values ( numbers in this simple example) in a column, Clm in my main data array, arsRef(__ , Clm )
Rs Clm= 1
1 __1
2 __9
3 __1
4 __8
5 __1
6 __7
I have an =Outer Loop== in my coding, controlled by a variable, rOuter, that goes, in this example, the first time, from 1 To 5. Picking out the last but top and bottom number from strRws, in this case 1 and 5 from “ 1 2 3 4 5 6 “ can be got in lots of ways. In previous routines I made a 1 dimensional array from using th Split function , and looked at the first an last but one element of that array. For no special reason I use a bit of string manipulation this time. By the way, the Immediate window is very useful for fiddling around to get at the right thing in coding such as this – see ImmediteWindowToHelpGetAtStuff.JPG : https://imgur.com/yGqmYSu : http://www.eileenslounge.com/viewtop...247121#p247121
Attachment 2205
A handy way of thinking about the bubble sort:
I think of the position of the outer loop at any time, as that as where my left hand. At any point in time, its position is determined by rOuter, and it is pseudo “holding” what is currently in that row number, rOuter = 1 2 3 4 or 5. ( Note I never “hold” the last row in my left hand )
At every of the 5 rows, my left hand waits there for a while, why my right hand in an -Inner Loop-- goes from one row above where my left hand currently is, rOuter+1 To the last row, 6
As I go “down” with my right hand holds temporarily each row value.
So at any point in the combined outer and inner looping, I have a value “in” each hand. This is effectively the middle point of the loop Here…
Basic Bubble sort loops
__For rOuter = 1 To 5 ‘ =====
___ For rInner = rOuter+1 To 6 ‘----
_____Here
______ Compare,
________ Possibly swap
____Next rInner ‘ ------
__Next rOuter ‘ =====
At Here, I compare the values in my hands. If the left hand, arsRef(rOuter, Clm) is > then the right hand, arsRef(rInner, Clm), then I swap the values.
If you consider what happens for one full inner loop, the first time, (rOuter = 1) , this process will mean that the smallest number anywhere ends up in my left hand.
As I move my left hand to row 2, and do the same process of moving my right hand down the remaining rows and comparing and possibly swapping values in my hand, then the next smallest ends up at row 2 .
As I move my left hand to row 3, and do the same process ……_
_….etc..
Doing the swap
I cannot in VBA, or in computing software generally, throw 2 values up in the air and catch them in the opposite hands. That is a nice Human ability to do things exactly at the same time. Most software just give the impression of doing things at the same time because it works so fast that we do not see every separate sequential thing. Our program must do a logical sequential progression: So to do the swap , ……
my left hand puts its value in a Temporary place:
____ then the left hand takes the value from the right hand:
_________finally the right hand takes the original left hand value from the Temporary place.
Swap all columns.
The reason for me doing the sorting is to re arrange all the rows. But what I have just explained has re ordered just one column, and not the entire row.
The main difference in this routine, Sub Bubbles , and Sub BubblesIndexIdeaWay is how I do this. For the current routine, Sub Bubbles , I do this in the usual way for an array type bubble sort.
At the inner point, Possibly swap Then, I don’t do the swap for just the column, Clm. ( I only use the variable Clm within the comparison line , Compare, If – I do this for the next routine also )
What I actually do is introduce another, third loop, which swaps all columns.
Recursion code designed to sort different row range
I cannot hard code the rOuter as 1 To 5 , and the rInner as rOuter+1 To 6 , not even for this simplified coding, as I want to use the same coding ( or rather further independent copies of it) , to sort sub row sections for the case of duplicate values in previously sorted columns.
Instead I use my array of row indices, Rs(). The intention will then be in Rem 3 to run a new copy of the recursion routine, Sub Bubbles , with the subset of Rs() and resort , ( and if necessary do this again to resort a further time by another column )
Each time I consider in total from the lower indicie to the upper indicie. ( rOuter from the lower, rInner to the upper ) The first time in the current example , this will be 1 To 6 , then it should be 1 To 3, and finally it should be 1 To 2 . ( It must not necessarily be, as in this example, that we always start at 1 )
Here is the final bubble sort code section
Code:
Dim rOuter As Long ' ========"Left Hand"=====================Outer Loop=====================================
For rOuter = Rs(LBound(Rs(), 1), 1) To Rs(UBound(Rs(), 1), 1) - 1 ' For first row indicie to last but one row indicie
Dim rInner As Long ' -------Inner Loop-------------"Right Hand"--------------------------
For rInner = rOuter + 1 To Rs(UBound(Rs(), 1), 1) ' from just above left hand through all the rest
'Here
If CDbl(arsRef(rOuter, Clm)) > CDbl(arsRef(rInner, Clm)) Then
Dim Temp As Variant ' I want to Swap those 2 above - I cant easilly in any coding change two values simulataneosly. So one of them Element values will put in this temporary place. This Element Values will then be given the other. Finally the other Element will be given this temporary value
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
Else
End If
Next rInner ' -----------------------------------------------------------------------
Next rOuter ' ==========================================================================================
_.___
As an intermediate step, lets end the routine for now at this stage, and run it via the Calling code
Intermediate coding for this is here: http://www.excelfox.com/forum/showth...ll=1#post11065
( Note that …_
For rOuter = Rs(LBound(Rs(), 1), 1) To Rs(UBound(Rs(), 1), 1) - 1
_... is only valid for this first copy run, --- typically less rows are used and we get them from like the strRws = “1 2 3 “ … )
If we run that using our test range we get these results:
| 1 |
5 |
3 |
a |
| 1 |
4 |
2 |
c |
| 1 |
3 |
2 |
e |
| 7 |
7 |
7 |
f |
| 8 |
8 |
8 |
d |
| 9 |
9 |
9 |
b |
| 1 |
3 |
2 |
e |
| 1 |
4 |
2 |
c |
| 1 |
5 |
3 |
a |
| 7 |
7 |
7 |
f |
| 8 |
8 |
8 |
d |
| 9 |
9 |
9 |
b |
Rem 3 - Preparation for possible recursion Call
Rem 3 Preparation for possible recursion Call
What we want to do
At this point, in many real applications we will likely have most of the rows sorted but will have possibly some rows with similar values in the column used in the initial sort.
We need some way to send arrange that the array as now sorted is resent again sorted by another running copy of the recursion routine over the duplicate rows.
We do not want to over simplify this coding. We did a bit of simplifying: For the sake of explanation of the general coding strategy it was OK to choose just numbers and all Ascending order. But to give the coding some worth in showing typical recursion coding workings, we should allow for the case of multiple duplicate row sections. We will discus that this is the sort of thing, that is to say going “back and forth” or “up and down” in a semi automatic way is what a recursion code is often best at.
There is no single way I know of, to write such a coding. It simply takes some careful thought to arrange that the Call of a new copy is such that on termination of that copy, the way the original copy resumes will allow for the possibility of things such as for “going back down” if, in this case, we need once again to resort multiple rows. Sometimes there are a couple of general characteristics which can help in the design of such a coding…
Main outer loop, and multiple copies of Copys/ “Levels”
Usually we will have a main outer loop, which in our case will be approximately across the entire range of current rows to be sorted. Often, but not always, towards the end, or last half, of the loop, will be the recursion Call, that is to say the code line which causes the current running routine to pause, whilst a new independent copy of the routine starts.
Having this Call part in a loop is probably what usually gives this ability to go “back and forth” between different independent copies of the recursion routine: You see , when a Called recursion routine copy ends, then the previous which had paused restarts, and depending on what your loop is doing, it will possibly cause the Call to be done again, so “you go back down” a copy level, or up a copy level depending on how you like to visualise it. It should be noted that each start and End of a recursion is an independent routine. The variable CopyNumber that we use tells us at “what level” we are, or how “far down” the recursion change of events. In our case that translates in the practice to which column in our list of columns to use , 1 3 2
CopyNo 1 : we are using column 1 to determine the sort order
CopyNo 2 : we are using column 3 to determine the sort order
CopyNo 3 : we are using column 2 to determine the sort order
There will only ever be one copy number 1 started, but that will likely pause a number of times. For further copy number , several may start and End: There can only ever be one running at any one time, but several independent copies could be ran from start to completion. The copy number is an indication of the “level” or in our case the column being used to determine the sort order. ( With hindsight… probably CopyLevel would be a better name for the variable… )
Ending
A typical, but not essential, characteristic of recursion routines is that not much usually goes on after the loop and / or last Call code line area. Further, a typical characteristic is that Ends, especially the final few Ends tend to occur one after the other. It tends to go unnoticed , for example when using debug F8 step code progression mode , since there Are not many code lines there. I find it therefore very demonstrative to have a Message box or Debug.Print code line immediately before the code end, with a message like “Ending a copy number “ & CopyNo & “”
Our specific case: What do we need to do
We already have the array we need for a possible required further sort in the case of duplicated values in the column used in the last bubble sort.
What we need to do is determine any rows with those duplicate values, and then pause the current routine whilst we run another copy of the routine after we have changed the indices in Rs() to just the sub set of rows to be sorted.
I guess there are many ways to do this. I expect I will come back some time and try a few.
The basic strategy to take advantage of recursion in this case will probably usually be the same
Basic recursion using strategy
This is not so difficult as the original sort will mean that rows for duplicated values in the current/ last sort column will be grouped together. So the basic strategy is to loop “down” and whilst we do this noting the indicies for these rows for duplicated values in the current/ last sort column. Once we have such a group use the Call Sub Bubbles(__ recursion starting code line to pause the current routine and start a new copy of the recursion routine to sort these rows which have duplicated values in the current/ last sort column.
Actual working application of the Basic recursion using strategy
I find it very convenient, as well as making debug easier , to “collect things” like row indices in a long string. This is because
it makes it easier to print or message box out what I have, so lends itself nicely to user interfaces to see what is going on,
and
there are very many string manipulating function and methods available
In fact, I already use the variable, strRws , for the range of rows, so it makes sense to use it to build my duplicated rows indicies in this sort of form like " 1 2 3 ". In other words similar I use it similar to how it was used in its first use , where, in this example, it was built like this in the Calling routine, " 1 2 3 4 5 6 ". For its first use it has all the indicies, and now , subsequently it will hold the indices corresponding to duplicate row values in the current/ last column used to define the sort order, in the latest version of arsRef() :
Just to clarify as it is easy to get lost in recursion routines: At this Rem 3 stage, I have most likely , just re ordered the array, arsRef(). It may have some rows that could not be put in any specific order if there were identical values in those rows in the column used in the last/ current sort.
One of the main workings of this section, Rem 3 , will be to obtain a string of the form " 1 2 3 " which represents duplicate row values in the current/ last column used to define the sort order.
( In a more realistic example , I might have several groups of such rows, so would have strRws = " 7 8 9 10 " , strRws = " 17 18 " , strRws = " 72 73 74 75 76 77 78 " , ..etc… Furthermore some of those rows might still need to be sorted using a different column / that is to say, using the next user given column, -- hence the reason for trying a recursion routine which “keeps going further and further” as needed
_ Let strRws = ""
I initialise my variable so that I can use it to build up my new duplicate variable rows list
__For rOuter = Left(Trim(tempStr), InStr(1, Trim(tempStr), " ", vbBinaryCompare) - 1) To …………………………
I begin a main **Outer Loop** for all but the last row, where the actual rows are determined by the upper and lower values in strRws
____ If strRws = "" Or InStr(1, Trim(strRws),…………….
____ I have a condition which should catch the situation of starting looking for a set of duplicates, so this will be at the start, strRws = "" , or if the last loop produced no addition to the string and so is left at a single indicia, meaning that no in between space is present, and consequently this will be 0 , InStr(1, Trim(strRws), " ", vbBinaryCompare). With these conditions met my string will become like " 1 " ( with more realistic data this could be any row number )
____ If Trim(UCase(CStr(arsRef(rOuter, Clm)))) = Trim(UCase(CStr(arsRef(rOuter + 1, Clm)))) …………………
____ This code line looks to see if we have a duplicate at the next row in last/ current sort column. ( This is why we loop in the main outer loop to 1 less than the last row, to prevent an error of array index out of range here )
_____ with the last condition met we add the indicie to the current string, strRws
_____ Let strRws = strRws & rOuter + 1 & " "
____ Else ' without the last condition met
____ without the last condition met, we might have the end of a group of duplicate rows, in which case it would be time to organise a recursion run so
____ If Not InStr(1, Trim(strRws), " ", vbBinaryCompare) = 0 Then
______we this we check for this situation needing a recursion run,
______with the condition met its time to organise recursion run
' Now its time to organise a recursion run
Because of how we built our string, strRws , we have nothing to do other than Call a new copy of the routine, Sub Bubbles( __ , with the appropriate arguments,
After that new copy of the recursion routine Ends, I will come back to just after the Call line and in a normal practical use I might still find another group of rows in this look at the first full row sorted array, so I set strRws=””
______ '+++*** this would be end of loop for most cases
Oh Fuck
In most cases I am finished after I am towards the end of the main Loop here.
But we have one slight problem: there is one small imperfection with our logic: In our logic, the end of a group of duplicates is determined by the two conditions:
_ firstly the next row is not a duplicate, and
_ secondly the current strRws has at least two indicies in it.
The problem comes if we have a group of duplicate rows that include the last row: In such an occurrence we will never reach a point where the next line being not a duplicate causes us to do the first then second check and subsequent Call of a new copy of the routine, Sub Bubbles( __.
To overcome this problem we include a last check which covers such an occurrence an allows a last Call of a new copy of the routine
I think for realistic real life data, this might be one of those situations whereby you ignore the last problem so as to either
use it effectively later to cause some mischief and then demand a high ransom to get correct it, or
or
you might consider arranging your data, such that the problem scenario would not occur. This might be a more efficient solution than having the extra check in every looping
In the next post is a summary of a run of the routines
( Final coding here:
http://www.excelfox.com/forum/showth...ll=1#post11067 )
Converting Sub Bubbles to Sub BubblesIndexIdeaWay
Converting Sub Bubbles to Sub BubblesIndexIdeaWay
This and the next posts will concentrate primarily on the changes required to convert the coding from the last posts, _...
http://www.excelfox.com/forum/showth...ll=1#post11063
http://www.excelfox.com/forum/showth...ll=1#post11064
http://www.excelfox.com/forum/showth...ll=1#post11066
http://www.excelfox.com/forum/showth...ll=1#post11068
_... into similar coding using the Index idea way.
http://www.excelfox.com/forum/showth...ll=1#post11062
http://www.excelfox.com/forum/showth...-VLookUp/page2
https://www.excelforum.com/excel-new...ml#post4571172
Global variables
I am not too keen generally on global variables , http://www.eileenslounge.com/viewtop...=29652#p229402 , but the alternative in our current coding would be to pass things in such a way as to arrange for By Referring to them throughout all passed routines. This makes things a bit messy due to a long signature line when we have a few variables, so I will use some global variables. But we note that it is not essential to the index way. It is done more for visual convenience and prettiness
These are the full set of global variable that should be placed at the top of a cod module
Code:
Option Explicit
Dim Cms() As Variant, Rs() As Variant ' "Horizointal Column" Indicies , "Vertica row" Indicies. ( The, "Vertical row" Indicies, ( and the "Horizointal Column" Indicies ) , can be Long or String types, and out App.Index coode line would work, but we typically use for convenience spreadsheet functions vial the VBA Evaluate(“ “) function to obtain these. As the Evaluate(“ “) returns all things housed in Variant type, we typically declared Rws() and Cms(), as Variant )
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
Dim arrIndx() As Variant ' For Sub SimpleArraySort7()
Sub Call_Sub_BubblesIndexIdeaWay()
We introduced the row indicies array, Rs(), in the last , although they were not really essential to have. We now require to use the complete variable associated with the Index idea way.
______ arrIndx() = Application.Index(arrOrig(), Rs(), Cms())
We need to add something to give us our column indicies , in this example for 4 columns , { 1 , 2, 3, 4 } , which we can conveniently get from a spreadsheet function, Column( ) , via Evaluate(“ “) in VBA
We also need to fill our original range data array, arrOrig(). This will simply be filled with the original data range, but will remain static.
We no longer need to pass any array: We will simply be able to reference the global variable , arrIndx() , for our final sorted output , after the routine, Sub BubblesIndexIdeaWay() has finally ended all copies of it ran after it being called by Sub Call_Sub_BubblesIndexIdeaWay()
arsRef() , arrIndx() WTF array
There is a lot of very similar arrays flying around at the moment.
Some will likely be redundant , or missing , in some codings. Often I will keep them to aid in coding version comparison, and their use may not be necessary, or , as mentioned full or partly redundant.
arrTS() : Up until now this was usually our array to be sorted and was taken from the spreadsheet capture, arrTS() = RngToSort.Value . As this was normally taken in as the array to be By Referred to , it was pseudo like the array carried in the array of the recursion function, arsRef() , and all changes were referred to this. It became finally the sorted array and would usually be then pasted out for demonstration purposes at some range offset from the original data. This pasting out ways usually the last line in the Calling routine, so became the last thing done typically.
arrIndx() : This is intended to be a global variable array, the array updated continuously with the new index way idea, arrIndx() = Application.Index(arrOrig(), Rs(), Cms()) . So this becomes , or is a replacement for , both all uses of arsRef() , and for the final usage of arrTS().
arsRef() : Mostly used as the pseudo “housing / carrying” array used throughout the recursion routines, passed between copies and continually being modified so that the “carried” array, usually arrTS() would finally have the final sorted data range array. For the index way idea, we have a couple of ways to use arrIndx():
_ We can pass this to be held in arsRef(),
_ We can simply change all references in the routine from arsRef() to arrIndx(). Because arrIndx() is a global variable, we will then always be referring to this.
Using the second option is the most sensible and efficient as then we can completely do without arsRef() , and indeed could completely remove it from anywhere, including the signature line of the recursion routine.
For the sake of consistency and comparison, I will use the first option. This will also be helpful in development of the next routine, Sub BubblesIndexIdeaWay(), as I will have an intermediate stage, with both arrays present, filled by the alternative ways, and can compare the results.
As arrIndx() will be passed to the recursion routine by Sub Call_Sub_BubblesIndexIdeaWay() , it needs to be filled initially, arrIndx() = arrTS()
All of these arrays are dynamic arrays of Variant type elements and so can be directly assigned to each other
Here is the almost the final Calling routine:
Code:
Sub Call_Sub_BubblesIndexIdeaWay() ' 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 ' This is somewhat redundant for this version and could be replaced by arrOrig()
Let arrTS() = RngToSort.Value
' Index idea variables
Let arrOrig() = arrTS()
Let arrIndx() = arrTS()
Let Cms() = Evaluate("=Column(A:F)") ' Convenient way to get
Let Rs() = Evaluate("=Row(1:6)") ' Initial row indicies
' Initial row indicies from full original range´of rows
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 BubblesIndexIdeaWay(1, arrIndx(), strRows, " 1 Asc 3 Asc 2 Asc ")
' Call BubblesIndexIdeaWay(1, arrIndx(), strRows, " 1 Asc ")
' Call BubblesIndexIdeaWay(1, arrIndx(), strRows, " 1 Asc 3 Asc ")
' Demo output
Let WsS.Range("B31").Resize(RngToSort.Rows.Count, RngToSort.Columns.Count).Value = arrIndx()
End Sub
_.____________________-
In the over next post the routine Sub BubblesIndexIdeaWay( __) will be developed. This will basically involve minor modifications to Sub Bubbles(__)
Before the final conversion , I will refresh and review the situation and add some extra lines to the above calling routine:
Row and Column indicies, Rs() and Cms()
For demonstration purposes, I will add a couple of extra final output lines to the calling routine, Sub Call_Sub_BubblesIndexIdeaWay()
These lines will simply paste out the initial and final row and column indices.
Refresh: Current stand of things, Initial Sub BubblesIndexIdeaWay as Sub Bubbles
Refresh: Current stand of things, Initial Sub BubblesIndexIdeaWay as Sub Bubbles
Before the final conversion , I will refresh and review the situation and add some extra lines to the above calling routine:
Row and Column indicies, Rs() and Cms()
For demonstration purposes, I will add a couple of extra final output lines to the calling routine, Sub Call_Sub_BubblesIndexIdeaWay()
These lines will simply paste out the initial and final row and column indices.
Initially I will run an intermediate routine , (which is a version of the last recursion routine) , from the below Calling routine. That version of the last recursion routine is here: http://www.excelfox.com/forum/showth...ll=1#post11071
That referenced routine is simply Sub Bubbles(), with the name changed to Sub BubblesIndexIdeaWay(), and the two recursion Calling code lines changed from
Call Bubbles(CopyNo + 1, arsRef(), strRws, strKeys)
to
Call BubblesIndexIdeaWay(CopyNo + 1, arsRef(), strRws, strKeys)
If I use this below calling routine, (Sub Call_Sub_BubblesIndexIdeaWay() ) , using our test data range ( http://www.excelfox.com/forum/showth...ll=1#post11073 ) , to Call the intermediate recursion routine ( http://www.excelfox.com/forum/showth...ll=1#post11071 ) , then I get the results shown.
Calling routine,
Sub Call_Sub_BubblesIndexIdeaWay()
Here: http://www.excelfox.com/forum/showth...ll=1#post11074
Original data range with added row and column indices
| Row\Col |
A |
B |
C |
D |
E |
|---|
10 |
|
1 |
2 |
3 |
4 |
11 |
1 |
1 |
5 |
3 |
a |
12 |
2 |
9 |
9 |
9 |
b |
13 |
3 |
1 |
4 |
2 |
c |
14 |
4 |
8 |
8 |
8 |
d |
15 |
5 |
1 |
3 |
2 |
e |
16 |
6 |
7 |
7 |
7 |
f |
Intermediate Output : Note row indicies are incorrect: They are not yet reordered. This is one of the main issues in the next post.
| Row\Col |
A |
B |
C |
D |
E |
|---|
30 |
|
1 |
2 |
3 |
4 |
31 |
1 |
1 |
3 |
2 |
e |
32 |
2 |
1 |
4 |
2 |
c |
33 |
3 |
1 |
5 |
3 |
a |
34 |
4 |
7 |
7 |
7 |
f |
35 |
5 |
8 |
8 |
8 |
d |
36 |
6 |
9 |
9 |
9 |
b |
Final conversion of Sub Bubbles to Sub BubblesIndexIdeaWay
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:
Code:
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
Code:
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
Code:
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
Code:
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
