AnsweredAssumed Answered

Can a function return a multidimensional array?

Question asked by Keaton Warn on May 7, 2019
Latest reply on May 7, 2019 by Josh Brady

After scavenging Google and coming up with very few relevant results, thought it would be worth asking here. Perhaps VBA is limited in this regard?


I realize a global, multidimensional array can be declared and initialized in a function, and then read/manipulated in Main or other functions. To me that's a valid workaround for some situations but not all.


Here's the workaround for anybody interested.


Option Explicit
Dim swApp As Object

Dim prArray(1, 2) As String
'It doesn't seem like VBA allows functions to return multidimensional arrays.
'A workaround is to declare a global multidimensional array and initialize it in another function or sub.
Sub main()

Set swApp = Application.SldWorks

    Dim MyArray(2, 3) As Integer
    Dim i As Long
    Dim j As Long

    i = 0
    j = 0

    Dim strArrayReturned(1, 2) As String
    'strArrayReturned = PassandReturn Error. Can't assign to array.
    For i = 0 To 1
        For j = 0 To 2
            Debug.Print "value of ("; i; ","; j; ") is: "; prArray(i, j)
        Next j
    Next i

    Debug.Print ""; ""
End Sub

Function PassandReturn() As String()
    'CURRENTLY GLOBAL Dim prArray(1, 2) As String
    prArray(0, 0) = "pr 00"
    prArray(0, 1) = "pr 01"
    prArray(0, 2) = "pr 02"
    prArray(1, 0) = "pr 10"
    prArray(1, 1) = "pr 11"
    prArray(1, 2) = "pr 12"
    PassandReturn = prArray

End Function