Difference between ByRef ByVal in Access Excel VBA

This tutorial explains difference between ByRef ByVal in Access Excel VBA.

Difference between ByRef ByVal in Access Excel VBA

ByRef means to pass a variable or Object by Reference (memory address). ByVal means to pass a variable or Object by Value.

When you define a Function with arguments, you can define the variable or Object as ByRef or ByVal. For example

Public Function functionByVal(ByVal x)
    x = x + 1
    functionByVal = x
End Function

or

Public Function functionByRef(ByRef x)
    x = x + 1
    functionByRef = x
End Function

or simply

Public Function functionByDefault(x)
    x = x + 1
    functionByDefault= x
End Function

By default, argument without ByRef or ByRef keyword is default to ByRef.

When we call all these Functions, both Functions have no difference at this point.

Public Sub test()
    y = 10
    MsgBox (functionByVal(10)) 'return 11
    MsgBox (functionByRef(10)) 'return 11
    MsgBox (y) 'return 10
End Sub

Pass Sub variable to Function argument

What happens if we pass the variable y into Function?

Lets see what happens in default behavior (ByRef)

Public Sub test()
    y = 10
    MsgBox (functionByRef(y))  'return 11
    MsgBox (y)  'return 11
End Sub

The original x in Sub becomes 10, which means passing y to Function affects the original y in Sub.

Lets see what happens in default behavior (ByVal)

Public Sub test2()
    y = 10
    MsgBox (functionByVal(y))  'return 11
    MsgBox (y)  'return 10
End Sub

Unlike ByRef, y is not affected by Function.

Explanation for difference between ByRef ByVal

ByRef is to pass memory address of variable from Calling Procedure to a Called Procedure.

Value of y in our case is stored in memory address, every time we call y, we point to the memory address to get the value.

As the memory address of y is passed to functionByRef() , any modification to the argument in the Function is made directly in the memory address, therefore when y is point to that memory again in test(), y changes.

When y is passed by value (ByVal), the value of y is passed to functionByVal(), not the actual memory address, so changes made to y in Sub or Function do not affect one another.

In summary, if you just want to make use of another Function to return a value, use ByVal in all cases.

Outbound References

http://www.vbdotnetforums.com/vb-net-general-discussion/32352-byref-vs-byval.html

Difference between ByRef ByVal in Access Excel VBA