VBA ByVal vs ByRef

When creating your function or subroutine, you will regularly need to pass arguments to it. You can choose to pass the arguments to the procedure either by value or by reference, with the keywords being ByVal and ByRef, respectively. What do these mean? What’s the difference between them? How do they work? And when should I use one over the other?

What is ByVal and ByRef?

When you create a procedure, you have to decide if the procedure will be accepting arguments or not. If your procedure does accept arguments, you can decide how it accepts those arguments.

For example:

Public Function Add(ByVal a As Long, ByVal b As Long) As Long
    Add = a + b
End Function

Public Sub MakeStringExciting(ByRef s As String)
    s = s & "!!!"
End Sub

In these procedures, we are using ByVal and ByRef explicitly. However, if you don’t specify which you want to use, like here:

Public Function AddThree(num As Long) As Long
    AddThree = num + 3
End Function

Then you’re using ByRef by default.

So what do these mean, exactly?

A Note About Data Types

Before we dive into this, it’s important to understand that there are value types and reference types when creating your variables. The basic idea is that simple data types like numbers, strings, and booleans are considered value types, where complex objects like Ranges, Worksheets, Charts, etc. are called reference types. You can think of a complex object as something that can have value types and reference types inside of it. For example, the Range data type is an object that has information about cell values, font type, color, border lines, other Range objects, etc.

How do you know if you’re dealing with a value type or a reference type? One simple way to tell is how you assign the variable for that type.

For example, look how we assign the Integer variable and the Range variable:

Public Sub AssigningTypes()
  Dim number As Integer
  Dim rng As Excel.Range

  number = 10
  Set rng = Range("A1")
End Sub

When you use the Set keyword to assign a variable, you’re dealing with a reference type.

Passing Arguments ByVal and ByRef with Different Data Types

There are subtle differences between using value types and reference types when passing data to a procedure using ByVal and ByRef. The table below describes the basics of what happens in each scenario.

Confusing? You bet it is! Don’t worry, with some examples, we’ll clear things up.

Quick note about “calling procedure” versus “called procedure” - when you call a procedure from another procedure, the procedure that you’re currently in is considered the called procedure and the procedure that invoked it is called the calling procedure.

Here’s an image to help explain. When we’re inside the DoStuff procedure (noted by the yellow highlight), the DoStuff procedure is considered the called procedure and the Main procedure is the calling procedure.

With all of this in mind, let’s dive into some examples.

Does this article help you? If so, please consider supporting me with a coffee ☕️

Passing Arguments Using ByVal

Using ByVal with Value Types

When passing a value type to a procedure using the ByVal keyword, you are essentially saying “hey, I would like to have a copy of the value.” Think of it like a copy machine. You can copy a piece of paper and hand it to someone. They can do whatever they want with that copy, but it doesn’t affect your original paper.

Let’s look at an example:

Public Sub ByValueExampleWithValueType()
  Dim str As String
  str = "hello"
  Call TryToChangeThisValue(str)
  Debug.Print str ' prints "hello"
End Sub

Private Sub TryToChangeThisValue(ByVal s As String)
  Debug.Print s ' prints "hello"
  s = "new message"
  Debug.Print s ' prints "new message"
End Sub

Here, we pass the string hello into the procedure TryToChangeThisValue. When we get into the TryToChangeThisValue procedure, we print the string hello to the Immediate Window. We then change the data in the variable s and print its value again, which now shows new message.

However, when we get back to the ByValueExampleWithValueType procedure, we print the str variable, which is still set to hello.

Passing a value type using ByVal lets us hand out a copy of the data, rather than give access to the original data itself. This is a good thing, since we may not want other procedures to be able to change variables in our main procedure.

Using ByVal with Reference Types

When you pass a reference type using ByVal, what you’re really saying is “please give me a copy of the reference.” What’s a reference? A reference is an address to the variable in memory on your computer. Think of it like a home address. You can give someone a home address and they can figure out how to get there. Once they get there, they can change things (like mowing your lawn or painting your house).

Let’s use another example to illustrate:

Public Sub ByValueExampleWithReferenceType()
  Dim cell As Excel.Range
  Set cell = Range("A1")
  cell.Value = "hello"
  Call ChangeCell(cell)
  Debug.Print cell.Value ' prints "new message"
End Sub

Private Sub ChangeCell(ByVal myCell As Excel.Range)
    Debug.Print myCell.Value ' prints "hello"
    myCell.Value = "new message"
End Sub

In this example, we set the cell variable to the A1 range on the current worksheet. We then change the value to hello. Then we pass the cell variable to the ChangeCell procedure using ByVal. Since cell is a reference type, what we really passed to ChangeCell is a copy of the reference to that same cell.

Once we’re in the ChangeCell procedure, we print the value of the range, which at that point is hello. Then we update the value of the cell to new message. After we get back to the ByValueExampleWithReferenceType procedure, we print the cell’s value again, which is now changed to new message.

Being able to modify the original data can be useful in certain scenarios. For example, say you needed to take a range of cells and format them in a certain way. The code for that might be hundreds of lines long. To me, it would be better to split that functionality out to keep your main procedure shorter and cleaner.

Why not just copy the whole object? Doing that can be an expensive operation on your computer and can make your code run slower.

Passing Arguments Using ByRef

Using ByRef with Value Types

When passing a value type to a procedure using ByRef, you are saying that you do want the reference to the original data. Going back to our copy machine analogy, this time instead of giving someone a copy of a paper, it’s like telling them which filing cabinet the paper is in. They can reach the original paper and make lasting changes to it.

Let’s change our string example above to use ByRef:

Public Sub ByReferenceExampleWithValueType()
  Dim str As String
  str = "hello"
  Call TryToChangeThisValue(str)
  Debug.Print str ' prints "new message"
End Sub

Private Sub TryToChangeThisValue(ByRef s As String)
  Debug.Print s ' prints "hello"
  s = "new message"
End Sub

Now, when we call TryToChangeThisValue using ByRef, the change on the string to new message also changes the str variable back in the ByReferenceExampleWithValueType procedure.

Why would you want to use ByRef for value types? Well, when using ByVal, the entire data contents are copied into the called procedure. If you have a value type that has a lot of data (for example, a string that is 65,400 characters long), it may be better to pass by reference if you need to optimize for speed and memory consumption.

Using ByRef with Reference Types

When passing a reference type to a procedure using ByRef, you are saying that not only do you want the reference to the original data, but you also can change the reference itself if you want to. This means that you can reassign the variable from the calling procedure to point to another object completely.

Here’s an example to help bring the point home:

Public Sub ByReferenceExampleWithReferenceType()
    Dim cell As Excel.Range
    Set cell = Range("A1")
    Debug.Print cell.Address ' prints $A$1
    Call ChangeCell(cell)
    Debug.Print cell.Address ' prints $A$2 - the cell variable has been reassigned
End Sub

Private Sub ChangeCell(ByRef myCell As Excel.Range)
    Debug.Print myCell.Address ' prints $A$1
    Set myCell = Range("A2")
    Debug.Print myCell.Address ' prints $A$2
End Sub

Let’s break this down step-by-step:

• We start off in the ByReferenceExampleWithReferenceType procedure.
• We set cell to the address A1 and print out that address in the Immediate Window.

• We then call ChangeCell and pass in the cellvariable using ByRef.

  • Inside ChangeCell, we first print out the address to show that it is pointing to A1 before we change it.
  • We then use the Set keyword to reassign the myCell variable to the new address A2.
  • We print out the new address of myCell which is now showing $A$2.
• Back in the ByReferenceExampleWithReferenceType procedure, we print out the address of our cell variable which now shows $A$2.

Reassigning the variable inside ChangeCell also reassigned it in the ByReferenceExampleWithReferenceType procedure because we used ByRef on a reference type.

If we tried to reassign the myCell variable inside ChangeCell when using ByVal, the reassignment would not be reflected in the calling procedure, as shown here:

Public Sub ByReferenceExampleWithReferenceType()
    Dim cell As Excel.Range
    Set cell = Range("A1")
    Debug.Print cell.Address ' prints $A$1
    Call ChangeCell(cell)
    Debug.Print cell.Address ' prints $A$1 - reassignment in ChangeCell not reflected here
End Sub

Private Sub ChangeCell(ByVal myCell As Excel.Range) ' changed ByRef to ByVal
    Debug.Print myCell.Address ' prints $A$1
    Set myCell = Range("A2")
    Debug.Print myCell.Address ' prints $A$2
End Sub

Passing a reference type to a procedure using ByRef gives you more control over the variable that came from the calling procedure.

What about Constants?

If the value type that you’re working with is a constant (by using the Const keyword), you cannot change the original value in the calling procedure, even if you were to use ByRef.

However, once inside your called procedure, you can modify the value of the constant that was passed in, even if you use ByRef. This means that the value you get inside the called procedure is not treated as a constant since you can change it.

Public Sub ConstantExample()
    Const message As String = "hi"
    
    Call ChangeStringByVal(message)
    Debug.Print message ' prints "hi"
    
    Call ChangeStringByRef(message)
    Debug.Print message ' prints "hi"
End Sub

Private Sub ChangeStringByVal(ByVal msg As String)
    msg = "new message"
    Debug.Print msg ' prints "new message"
End Sub

Private Sub ChangeStringByRef(ByRef msg As String)
    msg = "new message"
    Debug.Print msg ' prints "new message"
End Sub

Also, as far as I know, you cannot make reference types constants.

Choosing Between ByVal and ByRef

So how do you choose between ByVal and ByRef?

Technically speaking, all you need to know is if you’re working with value types or reference types, and from there you can determine what level of access you want to have in your procedures. Beyond that, it’s just a matter of preference.

For me, I like to think of it this way:

Personally, I default to ByVal most of the time for both value types and reference types. The main reason for this is because I prefer to have the main procedure own its data. I find it simpler to code and debug, but at the end of the day, it’s just a preference. I do believe there are good reasons to allow inner procedures to modify data it receives and sometimes you can’t avoid doing that anyway.

By the way, I do recommend to explicitly call out the keywords ByVal and ByRef in your procedure, so it’s clear which one you’re using. You can leave it out, but then you’ll need to remember that it’s ByRef by default.

Now that you have the details in your hands, you can figure out what works best for you.