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 ……. “ )
Reply With Quote
Bookmarks