Excel VBA call by reference bug?

Question

It seems that calling a function with an array as a parameter, don’t update the array (call by ref) if a return value is not used. Is this a bug or am I missing something thing here? A short test macro with the generated output below:

Sub Makro1()
'
' Makro1 Makro
'
' Kortkommando: Ctrl+t
'
    Dim xyzArray() As Double
    ReDim xyzArray(1 To 3)
    xyzArray(1) = 1
    xyzArray(2) = 2
    xyzArray(3) = 3
    
    Range("D2:F2").Value = xyzArray
    
    TestFunction (xyzArray)
    Range("D3:F3").Value = xyzArray
    
    newXYZ = TestFunction(xyzArray)
    Range("D4:F4").Value = xyzArray
    
    TestSub xyzArray
    Range("D5:F5").Value = xyzArray
    
    TestFunction (xyzArray)
    Range("D6:F6").Value = xyzArray

    TestFunctionNoReturn (xyzArray)
    Range("D7:F7").Value = xyzArray
  
End Sub

Private Function TestFunction(XYZ) As Double()
    XYZ(1) = XYZ(1) + 1
    XYZ(2) = XYZ(2) + 1
    XYZ(3) = XYZ(3) + 1
    TestFunction = XYZ
End Function

Private Sub TestSub(XYZ)
    XYZ(1) = XYZ(1) + 1
    XYZ(2) = XYZ(2) + 1
    XYZ(3) = XYZ(3) + 1
End Sub

Private Function TestFunctionNoReturn(XYZ) As Double()
    XYZ(1) = XYZ(1) + 1
    XYZ(2) = XYZ(2) + 1
    XYZ(3) = XYZ(3) + 1
End Function

[OUTPUT]

Answer 1

Your problem is a misunderstanding when to use parenthesis around arguments and when not.

If you call a function and read the result, you need to put the argument(s) in parenthesis:

newXYZ = TestFunction(xyzArray)

To call a sub, there are 2 different ways: Either you omit parenthesis, or you use the Call command. Both of the 2 following lines are doing the same:

TestSub xyzArray
Call TestSub(xyzArray)

It is not well known that you can do this also with functions (when you are not interested in the result):

TestFunction xyzArray
Call TestFunction(xyzArray)

However, the parenthesis in the following statements have a complete different meaning:

TestSub (xyzArray)
TestFunction (xyzArray)

As you can see, there is now a space between the routine name and the argument. That is because the parenthesis signals that you want to evaluate the term within (similar to a term like 3*(4+5)) the parenthesis before the call of the routine happens.

The VBA runtime will "evaluate" xyzArray. There is nothing to do, but evaluating means always that a new memory space is allocated to hold the result. In a term like 3*(4+5) this is obvious, you need a space somewhere to hold the result 27, but same is true for xyzArray: A copy of the data is created. Now you pass the copy per reference, this copy is modified from within your routine, but after the call this copy is gone as it is no longer used. The original content of xyzArray remains untouched, and that is your problem.

The syntax is sometimes (very rarely) used to prevent that an argument that is passed per reference can be modified by the routine. However, in almost all cases, it's simply done by mistake.

To prove the point that the parenthesis are not used to define the arguments, try the following:

Sub TestSub2 (XYZ As Variant, anotherParameter As String)
End Sub

(...)
TestSub (xyzArray, "Hello World")

This will raise a compiler error because the term (xyzArray, "Hello World") cannot be evaluated.