vba list 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 to arraylist

Function ArrayToArrayList(arr As Variant) As Object

    ' Check that array is One Dimensional
    On Error Resume Next
    Dim ret As Long
    ret = -1
    ret = UBound(arr, 2)
    On Error Goto 0
    If ret <> -1 Then
        Err.Raise vbObjectError + 513, "ArrayToArrayList" _
                , "The array can only have one 1 dimension"
    End If

    ' Create the ArrayList
    Dim coll As Object
    Set coll = CreateObject("System.Collections.ArrayList")
    
    ' Add items to the ArrayList
    Dim i As Long
    For i = LBound(arr, 1) To UBound(arr, 1)
        coll.Add arr(i)
    Next i
    
    ' Return the new ArrayList
    Set ArrayToArrayList = coll
    
End Function