VBA ByVal

ByVal is a statement in VBA. ByVal stands for "By Value," i.e., when the subprocedure calls in from the procedure, the value of the variables is reset to the new value from the new procedure called in.

In VBA, when we work with functions that call in different functions, there are certain circumstances that the value for the original argument is changed when calls the function. The ByVal statement prohibits the procedure or the code from changing the value for the argument.

By reading the explanation, we know it is not easy to understand. But with practical examples of function, we can understand it better.

Example #1

Look at the VBA codes below.

Code:

Sub Macro1()

    Dim k As Integer

    k = 50

    Macro2 k

    MsgBox k

End Sub

Sub Macro2(ByVal k As Integer)

    k = k + 5

End Sub

The above two macro procedures have a common variable, "k," across procedures. Let me explain this in detail before we see the result.

In the first Macro, we have assigned the value of 50 to the variable "k."

Dim k As Integer

k = 50

Next, we have called the second macro procedure from the first Macro.

Macro2 k

In Macro2, we have reset the variable's value to k = k + 5. In this macro, we have used the ByVal argument to assign the value to the variable "k."

Now, to understand "ByVal," let's run the VBA code by pressing the F8 key.

#1 - Upon pressing the F8 key first, it will highlight the first line of the Macro1.

At this point, place a cursor on the variable "k." It should show the value of the variable "k."

At the moment, the value of “k” is zero.

#2 - Press the F8 key again, and it will jump to the third line.

Even now, the value of "k" is still zero.

#3 - Press the F8 key now. See the value of the k.

Since the "k" value sets to 50 and the code executes, the value shows as 50.

#4 - The highlighted line is "Macro2 k," i.e., pressing the F8 key will jump to the second procedure, Macro2.

#5 - Even now, the value of variable "k" in this procedure also shows as 50. But inside this macro, we are resetting the value of the variable "k" as k = k + 5, i.e., 55. So, now press the F8 key two more times.

As you can see above, the "k" value is 55 now.

#6 - Press the F8 key. It will jump back to the Macro1 procedure.

When the macro jumps back to the original procedure Macro1, our variable "k" value is no longer 55 but rather the original value in this procedure, i.e., 50.

When you press the F8 key, we can see only 50 in the message box in VBA.

As we have told at the beginning of the article "ByVal," the argument does not carry values from one procedure to another even though it carries the variable's value from the first macro to the second by the moment. It encounters the line "ByVal" when it returns to the original macro. It resets the value to the original value in the procedure only.

Example #2

Now, take a look at the below two macros.

Code:

Sub P1()

    Dim k As Integer: k = 10

    Call P2(k)

    MsgBox k

End Sub

Sub P2(ByVal k As Integer)

     k = 15

End Sub

• It is similar to the first example. In the macro "P1," we have assigned the value of 10 to the variable "k," and in the same macro "P1," we have called the second macro "P2" with variable "k."
• In the second macro, "P2," we have used the ByVal argument. This time, the value of variable "k" is 15.

This macro carries the value of variable "k" as ten from macro "P1" to macro "P2." So, this macro will reset the value to 15, but the moment it comes back to finish the macro to the first macro, "P1," the value of "k" back to 10, not 15.

The ByVal argument does not affect the variable value even after running the macro. Still, we can carry the variable's value from one macro to the other with the By Ref argument.