What is ByVal vs ByRef ?

Ans : When arguments are passed to VBA functions,they can be passed in two ways.

A) ByVal :

☞ When argument is passed by Value, then it means that only value is passed to the procedure, and any changes that are made to the argument inside the
procedure will be lost when the procedure is exited

VBA Code Example :

Sub Test1()
Dim x As Integer

x = 5

Test2 x

MsgBox x

End Sub

Sub Test2(ByVal x As Integer)

x = x + 5

End Sub

Output : 5

B) ByRef:
☞ When the argument is passed by reference than the actual
address of the argument is passed to the procedure.

☞ Any changes that are made to the argument inside the
procedure will be recalled when the procedure is exited

☞ We can pass arguments to a procedure (function or sub)
by reference or by value.

VBA Code Example :

Sub Test3()
Dim y As Long

y = 50

Test4 y

MsgBox y
End Sub

Sub Test4(ByRef A As Long)

A = A * 10

End Sub

Output : 500 (50*10)

By default, Excel VBA passes arguments by reference.

Explanation:

☞ When passing arguments by reference we are
referencing the original value.

The value of y (the original value) is changed
in the function. 

Output = 500

☞ When passing arguments by value we are passing a
copy to the function.

The original value is not changed. 

Output = 5