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
    
    PassandReturn
    '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

Outcomes