how to convert arraylist to array in vba code example

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

Example 2: how to convert arraylist to array in vba

Imports System
Imports System.Collections


Public Class MainClass
    
    Shared Sub Main(ByVal args As String())
        Dim array_list As New ArrayList
        array_list.Add("A")
        array_list.Add("B")
        array_list.Add("C")

        ' Array of objects.
        Dim obj_array() As Object
        obj_array = array_list.ToArray()

        ' Array of strings.
        Dim string_array() As String
        string_array = DirectCast(array_list.ToArray(GetType(String)), String())

        ' Array object of objects.
        Dim astring_array As Array
        astring_array = array_list.ToArray()


    End Sub

End Class