Wednesday, 17 June 2015

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




No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts