VBA What's the underlying difference between call Sub or Function with or without parentheses

Question

I had an issue with passing an Array to a Sub By Reference, but the Array actually not get modified. I managed to fix that , but I want to know why.

Here is the example.

Private Function fCountArray(ByRef arrayVar As Variant)
    arrayVar(0) = 333
End Function

Sub Test1()
      Dim changedArr As Variant
      Dim notChangedArr As Variant

    ' create 2 quick array
      changedArr = Array(1, 2, 33, 56, 76, 89, 10)
      notChangedArr = Array(1, 2, 33, 56, 76, 89, 10)

    'intest = Array(3, 1, 2)
     fCountArray (notChangedArr)
     Debug.Print notChangedArr(0) 'Print Out 1

     fCountArray changedArr
     Debug.Print changedArr(0) 'Print Out 333
End Sub

So what is the underlying different between fCountArray (notChangedArr) and fCountArray changedArr

Why fCountArray (notChangedArr) Not pass by reference?

Answer 1

Great question! I believe the parentheses are causing an evaluation, so even though the function is taking the argument ByRef, you're not actually passing it the array local to the calling procedure, you're passing essentially a copy of it.

To avoid ambiguity, I tend to make Sub all procedures that don't explicitly return a value. And I use Function only to return values/evaluate an expression, never to manipulate objects/etc.

So I would do:

Sub fCountArray(ByRef arrayVar As Variant)
    arrayVar(0) = 333
End Sub

And then you can simply call that sub like:

fCountArray notChangedArr

(You may not need to make this Private, since it takes at least one required argument it will not be exposed to user in the macros dialog.)

I tested this also with String and observe the same behavior.

Update

This seems to confirm my suspicion, that a copy/temp instance of the evaluated expression is passed, not the ByRef variable instance.

http://msdn.microsoft.com/en-us/library/office/gg251769(v=office.15).aspx

Placing the argument in its own set of parentheses forces evaluation of it as an expression...The result of the evaluation is placed in a temporary location, and a reference to the temporary location is received by the procedure. Thus, the original MyVar retains its value.