VBA Copying, returning and passing arrays Passing Arrays to Proceedures
Example
Arrays can be passed to proceedures by putting () after the name of the array variable.
Function countElements(ByRef arr() As Double) As Long
countElements = UBound(arr) - LBound(arr) + 1
End Function
Arrays must be passed by reference. If no passing mechanism is specified, e.g. myFunction(arr()), then VBA will assume ByRef by default, however it is good coding practice to make it explicit. Trying to pass an array by value, e.g. myFunction(ByVal arr()) will result in an "Array argument must be ByRef" compilation error (or a "Syntax error" compilation error if Auto Syntax Check is not checked in the VBE options).
Passing by reference means that any changes to the array will be preserved in the calling proceedure.
Sub testArrayPassing()
Dim source(0 To 1) As Long
source(0) = 3
source(1) = 1
Debug.Print doubleAndSum(source) ' outputs 8
Debug.Print source(0); source(1) ' outputs 6 2
End Sub
Function doubleAndSum(ByRef arr() As Long)
arr(0) = arr(0) * 2
arr(1) = arr(1) * 2
doubleAndSum = arr(0) + arr(1)
End Function
If you want to avoid changing the original array then be careful to write the function so that it doesn't change any elements.
Function doubleAndSum(ByRef arr() As Long)
doubleAndSum = arr(0) * 2 + arr(1) * 2
End Function
Alternatively create a working copy of the array and work with the copy.
Function doubleAndSum(ByRef arr() As Long)
Dim copyOfArr() As Long
copyOfArr = arr
copyOfArr(0) = copyOfArr(0) * 2
copyOfArr(1) = copyOfArr(1) * 2
doubleAndSum = copyOfArr(0) + copyOfArr(1)
End Function
