[Solved] Defining and adding values to arrays inside an array in VBA Excel

by


Based on the description you’ve given, follow my suggestion.

Please, give us your feedback.

Private Function AmazingFunction(inputArray As Variant)
    Dim size As Long
    Dim i As Long
    Dim tmp As Variant
    Dim newArray() As Variant

    size = UBound(inputArray) ' Array size
    ReDim newArray(size) ' Resizes another array to the same size

    For i = 0 To size ' For each value
        tmp = CreateArray(length:=0) ' Allows you to dynamically create arrays
                                     ' In this case, an array of only one element is created.
        tmp(0) = inputArray(i) ' Sets the value of the first array element
        newArray(i) = tmp
    Next

    AmazingFunction = newArray
End Function

Auxiliary function to create arrays dynamically:

Private Function CreateArray(length As Long)
    Dim arr() As Variant
    ReDim arr(length)
    CreateArray = arr
End Function

Here is an example of how to use the function:

Private Sub UseTest()
    Dim outArray As Variant
    Dim intArray As Variant
    Dim element As Variant

    Dim inputArray(2) As Variant

    inputArray(0) = "a"
    inputArray(1) = "b"
    inputArray(2) = "c"
    outArray = AmazingFunction(inputArray)

    For Each intArray In outArray
        For Each element In intArray
            Debug.Print element
        Next
    Next
End Sub

1

solved Defining and adding values to arrays inside an array in VBA Excel