ByVal in VBA – Learn to Use in a Excel Macro

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.

1. First, we have specified the value 10 to the variable. We have specified this value in the procedure MACRO1.
2. After that, we called the second procedure MACRO2 from the first procedure. This runs the second procedure from the first procedure.
3. When we run the second procedure, we use the ByVal statement with the procedure’s name.
4. 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.
   
5. 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 Sub

Once 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 Sub

Now, 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.