The Difference between ByVal and ByRef in Excel VBA

The Difference between ByVal and ByRef in Excel VBA:

In VBA, ByVal (By Value) and ByRef (By Reference) are two different ways of passing arguments to the procedure. When a parameter is passed to the procedure as ByRef, the value of the parameter can change at runtime. However, if the parameter is passed as ByVal then its value does not change at runtime.


Use Coupon Code 580EDUNF83 to get additional 20% discount

Let us understand this by an example. Consider two procedures namely FIRST  and SECOND. Here, the FIRST procedure is called within the SECOND procedure. Hence the FIRST procedure is a CALLED procedure and the second one is CALLING procedure.

The FIRST procedure has integer argument "x" passed onto it as ByRef. This procedure adds 10 to the value of "x" passed onto it.

                                     Sub First(ByRef x As Integer)
                                     x = x + 10

                                     End Sub


The SECOND procedure does not have any arguments. It has a variable "y", the value of which is passed to FIRST procedure as argument and display the value of "y" in MsgBox.

                                            Sub Second()
                                            Dim y As Integer
                                            y = 5
                                            Call First(y)
                                            MsgBox (y)
                                            End Sub



ByRef:

Now, when we run SECOND procedure keeping "x" as ByRef in FIRST procedure the output would be 15.





    output for ByRef



ByVal:

Now, when we run SECOND procedure keeping "x" as ByVal in FIRST procedure the output would be 5. 




      Output for ByVal



Share:

1 comment: