ByRef ByVal (1 Viewer)

[kirkm]

I'm passing a string variable byRef into a Public Sub.
Its values changes in the subroutine and can read at Exit Sub
However when it returns to the code that called it, the variable is an empty string.

Just in case I had this wrong I tried byVal but it did the same thing.

This isn't right is it ? Should't it keep its changed value?

 

[arnelgp]

a Pointer to the string is passed to the sub
strP can be modified on the sub:

Public sub this_sub(Byref strP as string)
end sub

a Copy of the string is passed to sub
strP will never be modified:

Public sub this_sub(Byval strP as string)
end sub


if you leave out Byref/Byval, the default is Byref

 

[kirkm]

I did think the value ByRef could be changed in the sub. That isn't happening. Any idea what would make it fail ?

 

[kirkm]

I figured it out; either use Call or do not put the ByRef Var name in brackets.
That only held me up for 4 hours!

 

[Minty]

@arnelgp The default is ByVal - From Microsoft "The default in Visual Basic is to pass arguments by value. "

EDIT : This isn't correct - please see below comments.

 

[CJ_London]

Sorry @Minty, don't agree - the default is byRef (for VBA, VB might be different)

ByRef and ByVal

You can pass arguments to a procedure (function or sub) by reference or by value. By default, Excel VBA passes arguments by reference. As always, we will use an easy example to make things more clear.

 

[CJ_London]

either use Call or do not put the ByRef Var name in brackets

I never use call - it is a fallback to the early days of VBA. Call has to be used if you have multiple parameters and use brackets. Otherwise save some typing time, don't use call and don't use brackets.

 

[Minty]

Sorry @Minty, don't agree - the default is byRef (for VBA, VB might be different)

ByRef and ByVal

You can pass arguments to a procedure (function or sub) by reference or by value. By default, Excel VBA passes arguments by reference. As always, we will use an easy example to make things more clear.

@CJ_London I read that three times and thought, that's weird in my head I always thought it was the other way around, and that result page from here https://docs.microsoft.com/en-us/do...s/passing-arguments-by-value-and-by-reference is 3rd on the search results for VBA ! but as yo said applies to VB - you would have thought they would be consistent.

I only looked it up to check I wasn't going mad. More coffee required.

 

[CJ_London]

hmm - looking at the link you provided, I saw this interesting comment

If a parameter is declared with ByRef, the calling code can force the mechanism to ByVal by enclosing the argument name in parentheses in the call. For more information, see How to: Force an Argument to Be Passed by Value.

I presume that is by parameter, not all parameters e.g. Calculate(lowRate, (debtWithInterest))

 

[Isaac]

like has been mentioned, I almost never use Call, because then the additional syntax has to be remembered. I just use it without any By, and allow it to default. Then again, I don't usually care about trying to modify a passed in parameter to the function either. That would get so confusing! (I'm sure there is some place for it, I just don't happen to run across them).

 

[MajP]

To clarify what @CJ_London stated, this is a little understood feature of vba and I personally do not understand why it was done this way.
If you call a procedure and encapsulate the arguments in parentheses it will pass it as a value regardless of the called method. However if you use the call keyword then it will not automatically pass it by val. Using the Call keyword you can force it to pass byval by double encapsulating the arguments

someProc (X) ' passed by val regardless of called method
SomeProc (X),(Y) ' Passed each by val regardless of called method
Call SomeProc (x,y) ' Not automatically passed by val
Call SomeProc ((x),(Y)) ' passed by val regardless of called method



So if you had a procedure

Public Sub SomeProc(Ctrl as Access.Control)
'code
end sub

The following work
SomeProc Me.someCtrl
call SomeProc(Me.someCtrl)

This fails
SomeProc(Me.someCtrl)

What will happen above it will resolve me.someCtrl to whatever value is in the control.

So in other words the receiving method and the calling method, both can have a say in if it is passed byref or byVal.