VBA pass by reference or value

[lcpx]

Hi all,

I am still a bit confused about how VBA works when it Passing Argument by Reference or by Value.

Opposite to Java, by default VBA should pass argument by Ref. Please see the code below:

Function AddOne(ByRef x As Integer)
x = x + 1
End Function
Sub TestPass()
Dim y As Integer
y = 10
AddOne (y)
MsgBox y
End Sub


It displays 10 instead of 11, working exactly as passing by value. But if I change the code a little bit, take off the bracket in the TestPass sub, it will then displays 11. What is going on here?


Function AddOne(ByRef x As Integer)
x = x + 1
End Function
Sub TestPass()
Dim y As Integer
y = 10
AddOne y
MsgBox y
End Sub


Can anybody explain this? Many thanks.

[Bob Phillips]

When you pass a parameter in parentheses like that, the parameter is evaluted, so you are not passing the variable but its value, effectively passing it ByVal.