Access VBA Pass a MultiDimensional Array in a func

Question

Access 2013 32 bit: Win 7 64bit

Tried: Is it possible to pass a multidimensional array as a parameter in EXCEL VBA? to no avail

(If you can answer this you can probably answer that, but they're a little different)

Sub CreateArray()
    Dim myArray(1 to 10, 1 to 5)
    'Code that assigns values to (1 to 10, 1 to 4)
    myArray() = CalculateLastColofArray(myArray())
    'Do stuff with full array
End sub

Function CalculateLastColofArray(calcArray)
    For i = LBound(calcArray()) to UBound(calcArray())
        calcArray(i,5) = calcArray(i,1) + calcArray(i,3)
    Next i
    CalculateLastColofArray = calcArray()
End Function

My calculation is actually much more complex than the simple addition and my array is dynamically large (x, 5)

Doing it the way I have shown above fills myArray, but debugging has it shown as when I hover over it wrapped in the function call and it errors before entering the function

Answer 1

The key was the CalculateLastColofArray(myArray()) because I had included the parenthesis in the myArray() argument it was looking for a single value, not taking the array as a whole. changing this line to CalculateLastColofArray(myArray) solved my problem

it's always something small...

Answer 2

You can pass multidimensional array as a parameter.

You can also assign the result of the function to the array variable, however this variable must be declared as dynamic array (so its dimensions cannot be specified in declaring line).

Furthermore, you have some other errors in your code. Below is the correct version:

Sub CreateArray()
    'Dim myArray(1 To 10, 1 To 5) As Variant
    Dim myArray() As Variant

    ReDim myArray(1 To 10, 1 To 5)

    'Code that assigns values to (1 to 10, 1 to 4)
    myArray = CalculateLastColofArray(myArray)
    'Do stuff with full array

End Sub

Function CalculateLastColofArray(calcArray() As Variant) As Variant()
    For i = LBound(calcArray) To UBound(calcArray)
        calcArray(i, 5) = calcArray(i, 1) + calcArray(i, 3)
    Next i
    CalculateLastColofArray = calcArray
End Function