excelvba arraylist code example

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

'  
'
'

Tags:

Vb Example