excel vba array sort
'VBA does not include a built-in method for sorting arrays like many
'languages do.
'However, it is easy to have VBA create and use a .NET ArrayList for sorting.
'***THE MAJOR REASON TO USE ARRAYLIST IN VBA IS SORTING***
Set arrList = CreateObject("System.Collections.ArrayList")
arrList.Add 4
arrList.Add 2
arrList.Add 1
arrList.Add 3
arrList.Sort '<--Sort() uses the QuickSort algorithm
arr = arrList.toArray '<--toArray() creates a zero-based VBA array
MsgBox Join(arr, ",") '<--displays: 1,2,3,4
arrList.Reverse '<--Sort() uses the QuickSort algorithm
arr = arrList.toArray
MsgBox Join(arr, ",") '<--displays: 4,3,2,1
'ArrayList interrogation:
MsgBox arrList.Count '<--displays: 4. Count() returns 0 if Stack empty
MsgBox arrList.Contains("Hi") '<--displays: False
arrlist.Remove 3
arr = arrlist.toArray
Debug.Print Join(arr, ",") '<--displays: 4,2,1
'Clone the ArrayList:
Set arrlist2 = arrlist.Clone '<--arrlist2 is not affected by ops on arrlist
'Empty the ArrayList:
arrlist.Clear '<--arrlist now empty, but can take a new Add()
'NB: ArrayLists do not need to be dimensioned or redim'd like VBA arrays.
'NB: ArrayList elements can hold simple values as well as complex objects.
'Reference:
' https://docs.microsoft.com/en-us/dotnet/api/system.collections.arraylist