By Ref & By Val

[Davx]

In C++ the default in passing arguments to a sub is By Ref and not By Val. It seems VBA has it the other way round. Can someone confirm? If I want only a copy of the variable passed such that the variable itself may be preserved in the main code, I will normally achieve this using By Ref in C++ but this produces wrong results in VBA.

[Andy Pope]

The help file says.



by reference
A way of passing the address of an argument to a procedure instead of passing the value. This allows the procedure to access the actual variable. As a result, the variable's actual value can be changed by the procedure to which it is passed. Unless otherwise specified, arguments are passed by reference.




by value
A way of passing the value of an argument to a procedure instead of passing the address. This allows the procedure to access a copy of the variable. As a result, the variable's actual value can't be changed by the procedure to which it is passed.




ByRef is the default in Visual Basic

[mikerickson]

What are you passing?
Objects can only be passed ByRef.

[unmarkedhelicopter]

In C++ the default in passing arguments to a sub is By Ref and not By Val. It seems VBA has it the other way round. Can someone confirm? If I want only a copy of the variable passed such that the variable itself may be preserved in the main code, I will normally achieve this using By Ref in C++ but this produces wrong results in VBA.Surely you mean the other way round ?

[Paul_Hossler]

Don't know much (anything) about C++, but since so much of Excel involves passing objects (Range, Worksheet, ...) that ByRef would seem to be IMHO the appropriate default calling convention.

The way I read my little test below shows that the ByRef does pass the address and allows the passed variable to be modified, whereas ByVal only puts a copy of the variable on the stack.

Paul


'ByVal Optional. Indicates that the argument is passed by value.
Sub CalledByVal(ByVal x As Long)
x = 2 * x
MsgBox "CalledByVal: " & x
End Sub

'ByRef Optional. Indicates that the argument is passed by reference. 'ByRef is the default in Visual Basic.
'Sub CalledByRef(ByRef x As Long) or
Sub CalledByRef(x As Long)
x = 2 * x
MsgBox "CalledByRef: " & x
End Sub

Sub ABC()
Dim N As Long

N = 5

MsgBox "CalledByABC: " & N

Call CalledByVal(N)
MsgBox "CalledByABC: " & N
Call CalledByRef(N)
MsgBox "CalledByABC: " & N
End Sub