by Val, by Ref

[BittenApple]

Hello team,
I have stepped in through this code several times to see the difference between By Val, By Ref:

Code: Select all

Sub Test()
Dim x As Long
    ' Pass by value - x will not change
    x = 1
    Debug.Print "x before ByVal is"; x
    SubByVal x
    Debug.Print "x after ByVal is"; x

    ' Pass by reference - x will change
    x = 1
    Debug.Print "x before ByRef is"; x
    SubByRef x
    Debug.Print "x after ByRef is"; x
End Sub

Sub SubByVal(ByVal x As Long)
    ' x WILL NOT change outside as passed ByVal
    x = 99
End Sub

Sub SubByRef(ByRef x As Long)
    ' x WILL change outside as passed ByRef
    x = 99
End Sub

=====================
I can see By val the value is not chaned, when I step through it, I can't find it why. Can we say it is because machine/ computer is set this way?
Regards,
BittenApple

[AlanMiller]

If you had a
Debug.Print "x inside ByVal is"; x
INSIDE your SubByVal, after the line x = 99 then you should see "99". But once the sub exits, that LOCAL x vanishes along with its value. That is, that LOCAL x, passed only as a value of 1 then set to 99, goes out of scope once the sub exits. HTH

Alan

Edit: If you wanted to keep the 99, then the Sub would have to return a value and you'd need to use a Function instead, to achieve that result.
See http://www.excel-easy.com/vba/function-sub.html

Alan

[BittenApple]

Hello AlanMiller,

Thanks for the response!
I have stepped through and I understand what happens, I just to want to know when we say by val, computer knows that it needs to keep the value of the source.

I hope this makes sense.

Regards,
BittenApple

[AlanMiller]

Yes, your question makes sense. But a bit more in-depth will explain WHY things work this way. Passing arguments byRef and byVal do very different things. (This is not entirely accurate, as I've simplified the following using unsigned integer representation ... but doesn't affect the message though).

In your example, you have set x to have a value of 1 and stored as a Long (integer). Let's say it is stored at memory address 0xA634. It will occupy the next 4 bytes as 00000000000000000000000000000001. When you pass x ByVal, that actual number 00000000000000000000000000000001 will be passed to the Sub - the Sub will have no idea where it came from, or "permanently" lives. Anything in the Sub will be manipulating just that number, which will be stored separately, in an entirely different memory location, under what the Sub LOCALLY calls x.

When you call ByRef "x as Long" then it is the address 0xA634 which is passed to the Sub, along with the 4 byte size allocation. Any changes you then make to the x within the Sub will be written to the 4 bytes following that memory address. If you set x to 99, then the 4 bytes following address 0xA634 will become 00000000000000000000000001100011.

Hope that helps clarify the "WHY".

Alan

[BittenApple]

Alan,
In fact, it does make sense.
Thanks for this great explanation.
BittenApple

[AlanMiller]

Most welcome.

Alan