Passing Variable ByRef And ByVal in Excel VBA

Excel VBA ByRef and ByVal Syntax and Example

You can pass arguments to a Procedure (Function or Sub) By Reference or By Value. 

By default, Excel VBA passes arguments by reference. 

When we pass arguments By Reference , we are referencing the original value and value of the original value is changed in the Function.

When we pass arguments By Value , we are passing a copy to the function. The original value is not changed. 

Mehod-I : ByRef Example :

Sub By_Ref()

Dim X As Long

X = 20

MsgBox Cube_ByRef(X) ' Value of X after executed in Function

MsgBox  X  ' Initial/Original value of X

End Sub

Function Cube_ByRef(ByRef X As Long) As Long

X = (X * X * X)

Cube_ByRef = X

End Function

Output :

Value of X after executed in Function :

Initial/Original value of X :

As we used the ByRef method , the original value has been changed after executed in Function.

------------------------------------------------------------------------------------------------------------------------

Mehod-II : ByVal Example :

Sub By_Ref()

Dim X As Long

X = 20

MsgBox Cube_ByRef(X) ' Value of X after executed in Function

MsgBox  X  ' Initial/Original value of X

End Sub

Function Cube_ByVal(ByVal X As Long) As Long

X = (X * X * X)

Cube_ByVal = X

End Function

Output :

Value of X after executed in Function :

Initial/Original value of X :

As we used the ByVal method , the original value remain same and will not  change even after executed in Function.

Conclusion:

When we use the same variable in multiple instances in a Program blocks , still if we want to use the original value of the variable , we need to use the ByVal method.

Thanks, TAMATAM