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
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.
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
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
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
ByRef. The table below describes
the basics of what happens in each scenario.
|Value Type||Copies the underlying data into the called procedure. You cannot modify the original data in the calling procedure.||Gives the called procedure access to the original data. This means when you change a value in the called procedure, it will also be changed in the calling procedure.|
|Reference Type||Copies the reference to the called procedure. You can modify the contents of the object and those changes will be reflected in the called procedure.||Gives the procedure access to the original variable and all of its contents. This allows you to modify the the contents of the object and be able to reassign the variable which will be reflected in the calling procedure.|
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
procedure (noted by the yellow highlight), the
DoStuff procedure is considered the called procedure
Main procedure is the calling procedure.
With all of this in mind, let’s dive into some examples.
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
When we get into the
TryToChangeThisValue procedure, we print the string
hello to the Immediate Window. We then change the data in the variable
and print its value again, which now shows
However, when we get back to the
ByValueExampleWithValueType procedure, we print the
which is still set to
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
variable to the
ChangeCell procedure using
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
we print the cell’s value again, which is now changed to
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
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
ByRef, the change
on the string to
new message also changes the
back in the
Why would you want to use
ByRef for value types? Well, when using
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
- We set
cellto the address
A1and print out that address in the Immediate Window.
We then call
ChangeCelland pass in the
ChangeCell, we first print out the address to show that it is pointing to
A1before we change it.
- We then use the
Setkeyword to reassign the
myCellvariable to the new address
- We print out the new address of
myCellwhich is now showing
- Back in the
ByReferenceExampleWithReferenceTypeprocedure, we print out the address of our
cellvariable which now shows
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
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
you cannot change the original value in the calling procedure, even if you were to use
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
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:
|Value Type||Default to
If dealing with large data types (like long strings), consider using
If you do need to change the data and have it reflected back in the calling procedure, use
|Reference Type||Default to
If you need to be able to reassign the variable back in the calling procedure, use
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
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.