CODEWITHSUNDEEP
excelvbamultidimensional-array
Marc L
Marc L

Reputation: 877

Is it possible to pass a multidimensional array as a parameter in EXCEL VBA?

I have an array created in one procedure which is the final results, I want to utilise these results in a new procedure as inputs to the calculations there. Is there a way to pass a multidimensional array as a parameter into another procedure? How would you you put it into the call and how woudl you define the parameter in the procedure it will be used in?

Thanks

Upvotes: 3

Views: 10951

Answers (2)

Alex K.
Alex K.

Reputation: 175748

Pass as you would a single dimension array by decorating the routine argument with parentheses;

sub a()
    dim x(1, 1) As long
    x(0, 0) = 1
    x(1, 1) = 4
    process x
end sub

sub process(arr() As long)
    Msgbox arr(0, 0)
    Msgbox arr(1, 1)
end sub

Upvotes: 3

Chris Harland
Chris Harland

Reputation: 476

Is this the sort of thing you are after?

Sub AAATest()
''''''''''''''''''''''''
' Dynamic array to hold
' the result.
''''''''''''''''''''''''
Dim ReturnArr() As Long
Dim Ndx1 As Long
Dim Ndx2 As Long
Dim NumDims As Long
''''''''''''''''''''''''''
' call the function to get
' the result array.
''''''''''''''''''''''''''
ReturnArr = ReturnMulti()
NumDims = NumberOfArrayDimensions(Arr:=ReturnArr)
Select Case NumDims
    Case 0
        '''''''''''''''''''
        ' unallocated array
        '''''''''''''''''''
    Case 1
        ''''''''''''''''''''''''''
        ' single dimensional array
        ''''''''''''''''''''''''''
        For Ndx1 = LBound(ReturnArr) To UBound(ReturnArr)
            Debug.Print ReturnArr(Ndx1)
        Next Ndx1
    Case 2
        '''''''''''''''''''''''''''
        ' two dimensional array
        '''''''''''''''''''''''''''
        For Ndx1 = LBound(ReturnArr, 1) To UBound(ReturnArr, 1)
            For Ndx2 = LBound(ReturnArr, 2) To UBound(ReturnArr, 2)
                Debug.Print ReturnArr(Ndx1, Ndx2)
            Next Ndx2
        Next Ndx1
    Case Else
        ''''''''''''''''''''''
        ' too many dimensions
        ''''''''''''''''''''''
End Select
End Sub


Function ReturnMulti() As Long()
''''''''''''''''''''''''''''''''''''
' Returns a mutli-dimensional array.
''''''''''''''''''''''''''''''''''''
Dim A(1 To 2, 1 To 3) As Long
'''''''''''''''''''''''''''''
' put in some values.
'''''''''''''''''''''''''''''
A(1, 1) = 100
A(1, 2) = 200
A(1, 3) = 300
A(2, 1) = 400
A(2, 2) = 500
A(2, 3) = 600
ReturnMulti = A()
End Function

Upvotes: 0

Related Questions