ByVal and ByRef in VBA

[Niraj Baraili]

Hi All,

Need your help...when and how to use ByVal and ByRef in VBA ?

 

[Debaser]

ByVal passes a copy of a variable to a routine, so any changes to that variable will not be reflected in the original routine. ByRef passes a pointer to the variable, so any changes are reflected everywhere that variable is used.

In the case of objects, they are always passed as references (pointers) but if you pass an object ByVal you pass a copy of the pointer to the object rather than the original pointer.

Array typed variables are always passed ByRef.

 

[Niraj Baraili]

Hi Debaser,
Thank you for your reply.
As a newbee in VBA, i am still confussed. Can i have a example which will explain this theory.

 

[YasserKhalil]

Hello Niraj
Here's a tutorial I found in my library

'a. Formal Parameter - a variable passed to a Sub or Function (e.g. i, iRow, and sData are formal parameters).
'b. ByRef (default) means the Sub or Function is allowed to modify the value of a formal parameter and pass the _
modified value back to the calling routine.
'c. ByVal means means the Sub or Function is allowed to modify the value of a formal parameter (in some languages _
this causes a compile error), but the value that the calling routine sees is the ORIGINAL value.
'-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sub IncrementCounter(ByRef I As Long)
    I = I + 1
End Sub

Sub OuputValueToColumnA(ByVal iRow As Long, ByVal sData As String)
    Cells(iRow, "A").Value = sData
    iRow = iRow + 1                    'NOTE: Modification of a ByVal formal parameter is allowed inside the routine, but is poor programming practice
    Cells(iRow, "A").Value = sData
End Sub

Sub ImprovedOuputValueToColumnA(ByVal iInputRow As Long, ByVal sData As String)
    Dim iRow As Long
    iRow = iInputRow                  'Make a local copy of the input value
    Cells(iRow, "A").Value = sData
    iRow = iRow + 1                    'NOTE: Modification of a ByVal formal parameter is allowed inside the routine, but is poor programming practice
    Cells(iRow, "A").Value = sData
End Sub

Sub ByRefAndByVal()
    Dim iRow As Long
    Dim sMessage As String
   
    sMessage = "Hello World."
    iRow = 11
   
    Debug.Print "iRow = " & iRow 'Output to Immediate Window (CTRL G in debugger)
   
    Call IncrementCounter(iRow)
    Debug.Print "iRow = " & iRow '12 expected
   
    iRow = 21
    Debug.Print "iRow = " & iRow 'Output to Immediate Window (CTRL G in debugger)
   
    Call OuputValueToColumnA(iRow, sMessage)
    Debug.Print "iRow = " & iRow '21 expected
   
    Call ImprovedOuputValueToColumnA(iRow, sMessage)
    Debug.Print "iRow = " & iRow '21 expected
End Sub

 

[Debaser]

Here is a very simple example using a data type variable:

Sub foo()
    Dim i As Long
  
    i = 1
  
    Call Change_ByRef(i)
    ' i is changed by the Change_ByRef procedure and that change IS reflected here too because it was passed by reference
    MsgBox "i is now: " & i
  
    Call Change_ByVal(i)
    ' i is changed only within the Change_ByVal procedure because a COPY of it was passed
    MsgBox "i is still: " & i
  
End Sub
Sub Change_ByRef(ByRef lInput As Long)
    lInput = 14
End Sub

Sub Change_ByVal(ByVal lInput As Long)
    lInput = 21
End Sub

And here's an example using an object variable (a Range in this case). Note that when passed ByVal, you cannot change the orginal variable to make it point to a different object:

Sub Object_foo()
    Dim r As Range
  
    Set r = Range("A1")
  
    Call Change_object_ByRef(r)
    ' r is changed to a different cell by the Change_ByRef procedure and
    ' that change IS reflected here too because it was passed by reference
    MsgBox "r is now: " & r.Address
  
    Call Change_object_ByVal(r)
    ' r is changed to a different cell by the Change_ByVal procedure but
    ' that change IS NOT reflected here because it was passed by value.
    MsgBox "r is still: " & r.Address
  
End Sub
Sub Change_object_ByRef(ByRef rInput As Range)
    ' change the range variable to one row down
    Set rInput = rInput.Offset(1)
End Sub

Sub Change_object_ByVal(ByVal rInput As Range)
    ' change the range variable to one row down
    Set rInput = rInput.Offset(1)
End Sub

However, as the code below will show, even when passing ByVal, because you are still passing a pointer to the original object, you can change the properties of the object (you just can't change which object it is):

Sub Object_foo2()
    Dim r As Range
  
    Set r = Range("A1")
  
    Call Change_object_property_ByRef(r)
    ' r is changed to a different value by the Change_ByRef procedure and
    ' that change IS reflected here because an object refernce is passed
    MsgBox "r is now: " & r.Value
  
    Call Change_object_property_ByVal(r)
    ' r is changed to a different value by the Change_ByRef procedure and
    ' that change IS reflected here because an object reference is still passed
    MsgBox "r is now: " & r.Value
  
End Sub
Sub Change_object_property_ByRef(ByRef rInput As Range)
    ' change its value
    rInput.Value = "changed byref"
End Sub

Sub Change_object_property_ByVal(ByVal rInput As Range)
    ' change its value
    rInput.Value = "changed byval"
End Sub

Hopefully that makes sense.

 

[shrivallabha]

It is not mandatory to mention ByRef i.e. if nothing is mentioned then VBA will take argument as ByRef.

 

[Niraj Baraili]

Thanks all for your help.