Put ByVal before a parameter in the procedure’s declaration to pass a copy of the value. The called procedure can change its copy, and the caller’s variable keeps its original value:
<pre>Sub Main()
Dim n As Long
n = 10
AddFive n
MsgBox n 'still 10
End Sub
Sub AddFive(ByVal x As Long)
x = x + 5
End Sub</pre>
Without a keyword, VBA passes arguments ByRef, which hands over the variable itself. Remove ByVal from the example above and the message shows 15, because AddFive changed n.
ByVal also avoids the “ByRef argument type mismatch” compile error. A ByRef parameter declared As Long needs the caller to pass a variable declared As Long; with ByVal, VBA converts any compatible value.
Two things catch people out. Wrapping an argument in brackets, as in AddFive (n), passes a copy even to a ByRef parameter. And ByVal on an object such as a Range copies only the reference, so the procedure can still change the cells’ values and formatting.
What is ByVal
ByVal in VBA is used to get a variable with the original value assigned to it and use it in the current procedure. Let’s say a variable has a value of 20 in a procedure. Now, using ByVal, you can use that value in the current procedure, use that value as a base, and make changes to it. But you can’t change the original value.
How to use ByVal in VBA
In the example below, we have two procedures, MACRO1 and MACRO2. And in the MACRO1, we have a variable named “myNumber”. The best way to understand this is to understand the code.
So, to understand how ByVal works, we need to understand this code line by line.
- First, we have specified the value 10 to the variable. We have specified this value in the procedure MACRO1.
- After that, we called the second procedure MACRO2 from the first procedure. This runs the second procedure from the first procedure.
- When we run the second procedure, we use the ByVal statement with the procedure’s name.
- In the second procedure, we used the variable and added a value (5) to the existing value we got from the first argument. The variable’s original value was 10, and we added 5, meaning the current value will be 15.
- In the end, we have used the message box function to get the variable’s value. It showed 15 in the result, which is 10 + 5. 10 is the value we already have to the variable from the first procedure and 5 we have added to it in the current procedure.
'first macro
Sub MACRO1()
Dim myNumber As Integer
myNumber = 10
MACRO2 myNumber
End Sub
'second macro
Sub MACRO2(ByVal myNumber As Integer)
myNumber = myNumber + 5
MsgBox myNumber
End SubOnce the second (Current) procedure is complete, the variable’s value is changed to the value we assigned to it in the first procedure.
To make you understand this, we have changed the above code slightly. We have added a message box in the first procedure (MACRO1) after calling the second procedure (MACRO2).
'first macro
Sub MACRO1()
Dim myNumber As Integer
myNumber = 10
MACRO2 myNumber
MsgBox myNumber
End Sub
'second macro
Sub MACRO2(ByVal myNumber As Integer)
myNumber = myNumber + 5
MsgBox myNumber
End SubNow, in this code, when you run it, it shows you two message boxes, first from the MACRO2 and second from the MACRO1.
In the first message box, you can see it shows 15, which is the variable from the second procedure. It includes the variable value from the first variable and the added value from the second procedure.
After that, in the second message box, we have the variable’s original value assigned in the first procedure.
Written by
Puneet Gogia
Microsoft MVP — Excel · Founder, Excel Champs
Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.