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
