VBA Pass Array By Reference and Modify Contents

By default, arrays are passed by reference. I've seen in a few places that passing "ByVal" throws an exception. However, I have been able to use ByVal without issue in O365.

This is fairly trivial, using the ByRef keyword in the function signature will pass your array by reference rather than by value. This means that manipulations to that array will be preserved and bubble up to the calling scope. This is probably an oversimplification, but think of it this way:

• ByRef: you're passing a reference to the thing (array, Object, etc.) and any transformations to that thing will be apparent anywhere that thing exists.
• ByVal: you're passing the value representation of the thing. Essentially, you're passing a "copy" of it. You can manipulate this object, but it is not the same object from the calling scope, it's just a copy. So when the enclosing scope ends, you're still left with only the original thing.

Initialize your array as a numeric type and that will give you default 0 values.

Example as follows:

Option Explicit
Sub foo()
    Dim myArray(1 To 3) As Double  'The array is initialized with zero-values 

    Call bar(myArray)

    MsgBox myArray(3)

End Sub

Sub bar(ByRef dblArray() As Double)

    dblArray(1) = 123
    dblArray(2) = 345
    dblArray(3) = 33456

End Sub