VBA Copying, returning and passing arrays Passing Arrays to Proceedures


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