Access VBA Pass a MultiDimensional Array in a function

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

You have not declared the argument calcArray as an array. Try Function CalculateLastColofArray(calcArray()) — Scott Holtzman
– Scott Holtzman, Commented Dec 7, 2015 at 13:44
Hey Scott, thank you for the reply - that was the issue, but it was on the other side of the pass -- myArray() was being read as a single variant instead of as the array myArray, I had to change the call to myArray from myArray() — Schalton
– Schalton, Commented Dec 7, 2015 at 13:47
right. the issue was either way ... if you done what I suggested or what you did, either way would have worked. Basically, you were trying to call an apple an orange, and you needed to make them both apples to work :) Just thought it was worth noting so that you didn't think it could only be one way. — Scott Holtzman
– Scott Holtzman, Commented Dec 7, 2015 at 13:49

mielk · Accepted Answer · 2015-12-07 13:46:03Z

3

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

answered Dec 7, 2015 at 13:46

mielk

3,94014 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Schalton · Accepted Answer · 2015-12-07 13:45:19Z

0

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...

answered Dec 7, 2015 at 13:45

Schalton

3,1345 gold badges37 silver badges51 bronze badges

Collectives™ on Stack Overflow

Access VBA Pass a MultiDimensional Array in a function

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related