Trying to understand ByRef and ByVal

[spencer_time]

Hello again guys, I'm still trying to learn VBA and excel macros as best and fast as I can and one of the ways I have been doing that is posting my problems for you guys to help me with AND looking at others posts and attempting to help them with their problems.

I was trying to help someone and noticed the flag ByVal in front of a variable in a function and didn't know what it was.

Some quick google searching revealed that ByRef is the default and excel uses the argument as a reference, ByVal can be used instead to force excel to use the argument as a value instead.

I have experimented with both of them trying to understand the difference in a practical sense and I can't notice anything change.....

What I am asking is what is the difference between ByRef and ByVal and when would you want to use ByVal?

Thanks in advance for your time and help.

 

[mikerickson]

Its easier to understand ByRef and ByVal with values rather than objects.

The variables passed to a routine ByRef are effected by changes that routine makes. ByVal values do not cause changes in the calling arguments

Consider

Sub Test()
    Dim A as Long, B as Long

    A = 1
    B = Incriment(A)

    MsgBox A & vbcr & B
End Sub

Function Incriment(ByVal X as Long) As Long
    X = X + 1
    Incriment = X
End Function

That passes A to Incirment ByVal and adding 1 to the X argument does nothing to A.

But in this situation

Sub Test2()
    Dim A as Long, B as Long

    A = 1
    B = Incriment(A)

    MsgBox A & vbcr & B
End Sub

Function Incriment2(ByRef X as Long) As Long
    X = X + 1
    Incriment2 = X
End Function

The argument is passed ByRef and the X=X+1 effects both X and A.

 

[spencer_time]

Thank you mikerickson, that (although I suspect it had a typo, calling the same function from both test and test2 sub routines) cleared it up easier than a whole night of googleing could have done, I now understand that ByVal is used when you don't want your variable to change.

EDIT 22:43CST: fixed typo