This tutorial explains difference between ByRef ByVal in Access Excel VBA.
You may also want to read:
Difference between Dim and Set
Excel VBA Option Explicit to force declaration of variables
Function, Sub, Method, Property Differences
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
Public Function functionByRef(ByRef x) x = x + 1 functionByRef = x End Function
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.