ByVal And ByRef Difference in VBA With Example

In VBA Sub (procedures) and functions with arguments, you may have seen keywords like ByVal and ByRef before parameter name like the below image.

In this article, you will see the difference between ByVal (by value) and ByRef (By Reference) in VBA with examples.

ByVal in VBA

In VBA passing argument by ByVal (by value) copy of the original variable gets created in memory at a different location on RAM and this new copy of the variable behaves independently.

Since a copy of the original variable created hence passing by value consumes more memory than passing by reference.

Analogy:- Two or more people sharing different pieces of paper for writing.

In the above image in Sub test_1 val variable is passed by byVal (by value).

This test_1 is called by CallTest_1 procedure.

If you run CallTest_1 at 3rd line myVal=”ABC” a new variable got created.

At 4th line test_1 myVal while calling test_1 copy of a variable gets created.

4th and 5th line both will give the same message “ABC”. Since myVal variable is unaffected.

ByRef in VBA

In VBA passing argument by ByRef (by reference) one or more variables points toward the same memory location on RAM. 

ByRef is the default type of argument passing in VBA. 

Example:- val as String and ByRef val as String both are same

Thus a change in variable value at this location will be reflected in all variables referring to this memory location.

Since only variable names are different but memory location is the same hence this consumes less memory than passing by value.

Analogy:- Two or more people sharing the same piece of paper for writing.

Sub test_1(ByRef val As String)
  MsgBox val
  val = "XYZ"
End Sub
Sub CallTest_1()
  Dim myVal As String
  myVal = "ABC"
  test_1 myVal
  MsgBox myVal
End Sub

To understand the difference between ByVal and ByRef first copy the above code section in VBA IDE and run CallTest_1 Sub.

You will get two different messages 1st is “ABC” and 2nd is “XYZ”. But in the case of ByVal, both messages are the same. 

If you run CallTest_1 at 3rd line myVal=”ABC” a new variable got created.

At 4th line test_1 myVal while calling test_1 only new variable name gets created but this variable points to same RAM location.

Now, test_1 myVal will give the message “ABC”.

At the third line val=”XYZ” of test_1 Sub, variable value changes to “XYZ” from “ABC”.

Since myVal and val both variables point to the same RAM location hence myVal value will also get changed.

Hence at 5th line Msgbox myVal message box will display “XYZ” even if it has an initial value of “ABC”.

Objects can not be passed as ByVal in VBA

In VBA you can not pass objects such as Worksheet, Sheets, Pivots, Range, Shapes, ListObject Etc. as ByVal.

Hence the below code is not right and will throw a type mismatch error.

ByVal Wsht as Worksheet is not right. 

Running CallTst will throw an error. 

Sub tst(ByVal Wsht As Worksheet)
 msgxo Wsht.Name
End Sub
Sub CallTst()
  tst ThisWorkbook
End Sub

The following can be possible reasons for not passing objects as ByVal

• Objects have their own properties and values and making a copy of the object just burdens the RAM memory.
• It makes no sense to create a copy of an object, why create a copy if you want to modify the value of a property of that object.