ByRef and ByVal behave the same...

[MatthewGrace]

Hello and thank you for considering my question.

I've heard of passing arguments to a subroutine byRef or Value, but I'd never tried to use it. Now I want to. Seems easy enough.

My problem is that I was really sure that if you pass an argument to a subroutine by reference and it changes value, it would retain it's value even after returning to the calling procedure. It does not. Where the heck am I going wrong?

[MatthewGrace]

I've tested it using Functions instead of subs, I've tested it in standalone code modules instead of Form modules, no luck.

[Micron]

Tons of questions and answers on this. Rather than repeat what ten thousand other people have written on the subject, you'd be better off to research it.
Quick comment anyway: ByRef is default - consider it a pointer in memory that you can modify in other procedures that you pass it to. ByVal is a copy of the variable. You can pass it to another procedure and alter it there, but when execution returns to the calling procedure, the original variable remains unchanged.

EDIT - modified edits a lot here as I probably don't really understand what you're asking. Are the pictured comments yours or are they from whoever wrote that code? Maybe the blue text is what you need?
BTW, code is WAY better than pictures. Not only can we not do anything with the pic, it is often HUGE! and doesnt' need to be.

[MatthewGrace]

Thank you Micron. Next time, I will use code not a picture.

Yes, I have researched what other people are saying on it, that's the problem. I thought I knew what I was talking about - it seems very easy to understand the difference between byVal and byRef. Your retelling of their differences in your reply post is spot-on with my understanding.
The trouble is, I can't get results in step wtih my understanding.

I wrote the code in the above. My belief is that the "Aftermath value" should be 10, not 5. I cannot see why it isn't 10.

I clearly modified x byReference to be 10. I confirmed this with a debug.print statement which says 'Returns 10

Then, amazingly, as soon as we finish our final debug.print statement, we're back to 5 again. I would have expected that from byVal, not byRef.

I feel like there's something obvious I'm doing wrong, but I can't figure out what.
matt

[accesstos]

Remove the parentheses from the calling of the procedure or use the Call statement.

refOrVal x
'or
Call refOrVal(x)

[MatthewGrace]

Holy Cow. That fixed it.

Not a damn clue why, but that fixed it.

Thank you.

[accesstos]

You are welcome!

[Micron]

Good catch.
I believe the reason is that using parentheses (ps for now) means you expect a return from the function. However, I'm surprised it allowed you to write it that way and here's why:

Usually you use ps with function calls to assign the return value from a function to a variable. Consider msgbox function.
msgbox "Hello" is OK
**msgbox("Hello") will raise an error. This is because there is no variable to which the function can return a value.
result = msgbox("Hello") would not raise an error. The value of result is usually intended to be the number or constant returned by the button that was clicked.
So the only times you can use ps with a function call is to assign the return value to a variable OR
you use the Call statement as in Call msgbox("Hello"). Normally you wouldn't write it that way, but it won't raise and error.

About 6 months ago (after all these years) I learned that a function has a return value even if not specified and yours doesn't. However I can't recall if it returns a Variant or a String. I would not have guessed that the way you wrote it would get the function to return a value when using ps and no variable. It certainly will not work with MsgBox function. Perhaps it only works with custom functions, but I sort of doubt that. So I'm still intrigued.

**this is incorrect. Since I cannot recall the error I've been able to raise in the past I'm going to stop messing up this thread. I'll end by saying that at least I did find an explanation that states that a function call wrapped in ps causes it to be passed as if ByVal.

This also explains why calls with a variable passed ByRef act as if called ByVal if the argument is enclosed in parentheses.

See #59 here, which is about the best answer I've ever seen on the subject:

https://stackoverflow.com/questions/...function-calls

[accesstos]

This thread become interesting!

Matthew, thanks a lot and congratulations for the observation!
Micron, thanks a lot for the persistence and the research on the subject!

From the useful answer (which until now has get 59 votes) I keep this point:

If you use parentheses in any other context but to enclose procedure call arguments, VB(A) will attempt to evaluate the expression in the parentheses to a resulting simple value.

That's explains why the calling of a procedure with a single argument enclosed in ps without the Call statement is allowed. msgbox("Hello") doesn't raise an error (single argument) but msgbox("Hello",vbOKOnly) do. ("Hello") = "Hello" but ("Hello",0) drives in Error.

Sub TestMyMessage()
    Dim strIn As String
    
    strIn = "Hello world!"
    MsgBox ("MyMessage() Is Type " & TypeName(MyMessage(strIn)))
    
End Sub

Function MyMessage(ByRef strMsg As String)
    MsgBox (strMsg)
End Function

Also, that's explains why VBA pass as ByVal the argument to the calling procedure, because is passing the result of the evaluation and not the pointer to the variable of the caller, and the worst of this fact is that this is applicable to any case of Byref arguments, even in correctly calling procedures with more than one argument.

Try this:

Sub TestMultiply()
    Dim intX As Integer
    Dim intY As Integer
    
    intX = 5
    intY = 2
    
    Debug.Print "intX before=" & intX
    Debug.Print "intY before=" & intY
    
     Call RefMultiply(intX, intY + 2)
     
    Debug.Print "intX after=" & intX
    Debug.Print "intY after=" & intY
    
End Sub

Sub RefMultiply(ByRef x As Integer, ByRef y As Integer)
    Debug.Print "   RefMultiply: x*y(" & x & "*" & y & ")=" & x * y
    x = x * y
End Sub

TestMultiply produce this:

intX before=5
intY before=2
   RefMultiply: x*y(5*4)=20
intX after=20
intY after=2

So, the most important that we have to keep in mind is that, any expression in place of a ByRef argument, puts a new value in place of the pointer and therefor we must to avoid them.

I have to concede that I never realized that until now and I have to say that I learned something today!

Cheers,
John

[Micron]

John, thanks for the very detailed explanation. I'm self taught in VBA (I suppose everyone is?) and have been at it for about 15 years and still struggle at times. Still only know and understand about 20% of it, I'd say. I didn't realize 59 was points, so I guess that will change. Should have used the poster name or posted date instead.

It's going to bug me for a bit as to what msgbox syntax raised an error in the past when I tested msgbox calls.

[CJ_London]

Using Call is a fallback for backward compatibilty to the very early days of VBA. It is no longer required. Any code you pick up from the web that uses Call is going to be 20+ years old.

for subs, if you have a single parameter, just use ps (but you can use call if you wish). However if you have multiple parameters, you can use call and ps, or you simply don't use call or ps (which is the 'modern' way)

for functions, they can work like subs - i.e. don't use ps or if you want to make use of the returned value, use ps. Note that making use of the returned value does not mean it needs to be assigned to another value

result=msgbox("hello",vbyesno)
if result=vbyes then dosomething


or you can simply use
if msgbox("hello",vbyesno)=ybyes then dosomething