vba excel array to list code example

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