Function to return 2 arrays as output

Function Arrays1_2(i As Variant) As Variant
Dim ar(1 To 2) As Variant

ar(1) = "A"
ar(2) = "B"

Arrays1_2 = ar(i)

End Function

In Excel Cell

=Arrays1_2(1)

Or

=Arrays1_2(2)

Or

For auto increment try this...

=Arrays1_2(ROWS($1:1))

Drag it down...

A function can't return two separate arrays. You can either return a 2D array, or you can pass one (or both) of the output arrays to the routine so that it can alter the passed variable directly.

A function can return two arrays, by using a ByRef argument.

Sub test()
    Dim firstReturn As Variant
    Dim secondReturn As Variant

    firstReturn = myFtn("x", secondReturn)
    
    MsgBox Join(firstReturn) & vbCr & Join(secondReturn)
End Sub


Function myFtn(aString As String, Optional ByRef secondArray As Variant) As Variant
    Dim Result1(1 To 3)   As String
    Dim Result2(1 To 3) As String
    Dim i As Long
    For i = 1 To 3: Result1(i) = aString & i: Next i
    For i = 1 To 3: Result2(i) = i & aString: Next i
    
    myFtn = Result1
    secondArray = Result2
End Function

Hello Kyle/mikerickson,

Thanks for answer. I've tried both solutions and I've been able to adapt mike's solution but not Kyle´s solution.

For Kyle's solution I have issues assigning the values to the arrays within the function and for mike's solution, it seems to work
fine, but the numbers are stored as string, I'm not sure why. I'm modified I little bit your codes since the input of the function is
a variant array of n x m dimensions.

This is what I've done:
Kyle´s solution: (I get wrong number of dimensions inside the function)

Sub test_Kyle()
Dim arr, myArray1, myArray2

a = [{1,2,3;4,2,1;10,2,7}]

arr = testArr(a)
myArray1 = arr(0)
myArray2 = arr(1)
End Sub

Function testArr(MyArr As Variant) As Variant
    Dim arr(1, 1)
    
    For i = 1 To 3
        For j = 1 To 2
            arr(0)(i, j) = MyArr(i, j) + 1
            arr(1)(i, j) = MyArr(i, j) + 2
        Next j
    Next i

    testArr = arr
End Function

mikerickson´s solution:

Sub test_mikerickson()
    Dim firstReturn As Variant
    Dim secondReturn As Variant
    a = [{1,7.3,3;4,4,1;10,0.9,7}]
    
    firstReturn = myFtn(a, secondReturn)
    'MsgBox Join(firstReturn) & vbCr & Join(secondReturn)
End Sub

Function myFtn(MyArray As Variant, Optional ByRef secondArray As Variant) As Variant
    Dim Result1(1 To 3, 1 To 2)  As String
    Dim Result2(1 To 3, 1 To 2) As String
    Dim i As Long
    For i = 1 To 3
        For j = 1 To 2
            Result1(i, j) = MyArray(i, j) + 1
            Result2(i, j) = MyArray(i, j) + 2
        Next j
    Next i
    
    myFtn = Result1
    secondArray = Result2
End Function

Thanks again for the help

I'm glad you've got it to work.
There's one more tweek you might want. If you are returning the array to a worksheet formula, you could add an Index argument to specify which array you want returned.

Function myFtn(aString As String, Optional returnIndex as Long = 1, _
          Optional ByRef firstArray As Variant, Optional ByRef secondArray As Variant) As Variant
    Dim Result1(1 To 3)   As String
    Dim Result2(1 To 3) As String
    Dim i As Long
    For i = 1 To 3: Result1(i) = aString & i: Next i
    For i = 1 To 3: Result2(i) = i & aString: Next i
    
    firstArray = Result1
    secondArray = Result2

    If returnIndex = 1 Then
        myFtn = firstArray
    ElseIf returnIndex = 2 Then
        myFtn = secondArray
    Else
        myFtn = CVErr(xlErrNum)
    End If
    
End Function