vba sort array code example

Example: 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