Add an Arraylist version to the ..... List, collection, of ways
Function SortInOrderArrayListStylio(
Code:
Sub MesnbVersion() ' http://www.excelfox.com/forum/showthread.php/2130-Sort-an-array-based-on-another-array-VBA?p=9972#post9972
Dim sn() As String, sp() As String ' VBA Strings Split Function used below which returns String types
sn() = Split("aa bb ii cc dd aa ee cc ff gg hh ii") 'This Array is an Array to be sorted in an order which depends on its Element values
sp() = Split("ii hh gg ff ee dd cc bb aa") 'This Array shows the order in which the Element values should be made
Dim Results() As String 'Arrays into the Function and out will have String type Elements ( are "housed" in transport in a variant Variable *** )
Let Results() = SortInOrder(sn, sp) ' ( Array 2 be sorted in Order , Array showing wanted order )
MsgBox Prompt:=VBA.Strings.Join(Results, " ")
MsgBox Trim(F_sort(sn, sp))
Dim VarResults() As Variant ' 1 dimensional Array returned by .ToArray Methhod returns Variant Elements
Let VarResults() = SortInOrderArrayListStylio(sn, sp) ' ( Array 2 be sorted in Order , Array showing wanted order )
MsgBox Prompt:=VBA.Strings.Join(Results, " ")
End Sub
' ( Array 2 be sorted in Order , Array showing wanted order )
Function SortInOrder(ByVal Array2BSorted As Variant, ByVal ArrayOrder2Sort As Variant) As Variant ' For ByVal Arrays must be "housed in Variant Variables. *** http://www.excelforum.com/showthread.php?t=1101544&page=10#post4381420 The only Type that can return an Array is variant so that must be the type of the Function
Dim IndxA2B As Long, IndxOrdr As Long, IndxASInOdr As Long: Let IndxASInOdr = -1 ' ' Index for Array to be sorted, Array showing order, and final sorted Array: Index for final sorted Array will be incremented by 1 just before next entry, and we are using base 0 1 Dimensional Arrays throughout, so before the first increment it should have -1 so that adding 1 gives zero, the first required index in the Array
Dim arrSortInOrder() As String: ReDim arrSortInOrder(LBound(Array2BSorted) To UBound(Array2BSorted)) ' !!! All Entries in the Array to be sorted are used , including duplicates in the Final Array, so the Output Sorted Array will have the same size as the Input Array to be sorted
For IndxOrdr = LBound(ArrayOrder2Sort) To UBound(ArrayOrder2Sort) 'In order of the Sorted order , IndxOrdr ...
For IndxA2B = LBound(Array2BSorted) To UBound(Array2BSorted) '...each Element in the Array to be sorted is considered and...
If Array2BSorted(IndxA2B) = ArrayOrder2Sort(IndxOrdr) Then '... when we find it ( we will eventually !!! ) we
Let IndxASInOdr = IndxASInOdr + 1 '... increase the index in the final sorted Array, and...
Let arrSortInOrder(IndxASInOdr) = Array2BSorted(IndxA2B) 'put the found matched Element in the Final Sorted Array. As we go in to the next Element in the Array to be sorted, we will catch any duplicate and that will be put in the next Element of the Final sorted Array untill all Elements are checked ..Then...we...
Else ' case of not found the current element to be sorted yet ( we always do !!! ) Redundant code line
End If
Next IndxA2B ' keep going along the Array to be sorted !!! - we will eventually find the Element with the value of the current Element considered from the Array showing order
Next IndxOrdr '... go back to look for all Elements in the Array to be sorted which match the next Element in the Order to be sorted Array
Let SortInOrder = arrSortInOrder() ' The final Array is put in the Variant variable SortInOrder ( effectively "returning" it at end of Function - or rather as the code moves on past the Function the Array is in that Variable - Function( ) as Variant is like Dim Function as Variant, and the course of the function does the Let Function = bit here )
End Function
Function F_sort(sq, st)
Dim j As Long, jj As Long
For j = 0 To UBound(st)
For jj = 0 To UBound(sq)
If sq(jj) = st(j) Then F_sort = F_sort & " " & sq(jj)
Next
Next
End Function
'http://www.snb-vba.eu/VBA_Arraylist_en.html
Function SortInOrderArrayListStylio(ByVal Array2BSorted As Variant, ByVal ArrayOrder2Sort As Variant) As Variant ' For ByVal Arrays must be "housed in Variant Variables. *** http://www.excelforum.com/showthread.php?t=1101544&page=10#post4381420 The only Type that can return an Array is Variant so that must be the type of the Function
Dim IndxA2B As Long, IndxOrdr As Long ' ' Index for Array to be sorted, Array showing order
Dim a_00 As Object
Set a_00 = CreateObject("System.Collections.ArrayList") 'Without a reference to the library: 'late binding' with the use of a variable: http://www.snb-vba.eu/VBA_Arraylist_en.html#L_4.2.1
For IndxOrdr = LBound(ArrayOrder2Sort) To UBound(ArrayOrder2Sort) ' In order of the Sorted order , IndxOrdr ...
For IndxA2B = LBound(Array2BSorted) To UBound(Array2BSorted) ' ...each Element in the Array to be sorted is considered and...
If Array2BSorted(IndxA2B) = ArrayOrder2Sort(IndxOrdr) Then '... when we find it ( we will eventually !!! ) we
a_00.Add (Array2BSorted(IndxA2B)) 'Add to the Array list - automatically at next position along
Else ' case of not found the current element to be sorted yet ( we always do !!! ) Redundant code line
End If
Next IndxA2B ' keep going along the Array to be sorted !!! - we will eventually find the Element with the value of the current Element considered from the Array showing order
Next IndxOrdr '.. . go back to look for all Elements in the Array to be sorted which match the next Element in the Order to be sorted Array
Let SortInOrderArrayListStylio = a_00.toarray() ' The method .ToArray writes all elements of the ArrayList into a 1-dimensional Array. ' http://www.snb-vba.eu/VBA_Arraylist_en.html#L_9.3 ( effectively "returning" it at end of Function - or rather as the code moves on past the Function the Array is in that Variable - Function( ) as Variant is like Dim Function as Variant, and the course of the function does the Let Function = bit here )
End Function
But why do i have to do this
a_00.Add (Array2BSorted(IndxA2B))
and not this
a_00.Add Array2BSorted(IndxA2B)
Hmmm...
_.. I have to "evaluate it" ?? to return its Value ??
'Rem Ref http://www.snb-vba.eu/VBA_Arraylist_en.html
' http://www.excelfox.com/forum/showth...=9991#post9991
Reply With Quote
Bookmarks