excel vba arraylist sort code example

Example 1: excel vba arraylist

'VBA does not include a native ArrayList memory structure. 

'However, VBA can create and use a .NET ArrayList:

Set ArrayList = CreateObject("System.Collections.ArrayList")
  	ArrayList.Add "World" 
  	ArrayList.Add "Hello " 
	ArrayList.Sort
  	MsgBox ArrayList(0) & ArrayList(1)		'<--displays: Hello World
  
'Notes: Since this calls .NET, an ArrayList is slower than a VBA array.
'       The most compelling reason to use an ArrayList is the built-in 
'       Sort method. VBA arrays do not have a built-in sort capability.
'       Neither do collections or dictionaries. But, the .NET ArrayList 
'       does.
'
'       The .NET ArrayList also includes a 'ToArray' method:
  
  	vArr = ArrayList.ToArray
  
'		This will create a normal 1D VBA array with a lowerbound of zero
'       where the elements coincide with the items in the ArrayList.
  
'       The .NET ArrayList has many properties and methods:
'       https://docs.microsoft.com/en-us/dotnet/api/system.collections.arraylist  

'  
'
'

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