VBA ByVal

Updated on January 1, 2024
Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

Excel VBA ByVal Function Argument

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.

VBA-ByVal

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VBA ByVal (wallstreetmojo.com)

How to use ByVal Argument in VBA?

You can download this VBA ByVal Excel Template here – VBA ByVal Excel Template

–>> If you want to learn Excel VBA professionally, then our VBA Basic Course (16+ hours) is the perfect solution. In our Basic Excel VBA course you learn the skill of automating tasks using Variables, Data Types, Ranges, and Cells in VBA. Master Control Structures, including Conditional Statements and Loops, and discover techniques for manipulating data through sorting, filtering, and formatting. By the end of the course, you will be able to apply your acquired skills to real projects, culminating in an Excel VBA project which will solidify your ability to create efficient, automated solutions.

Example #1

Look at the VBA codesVBA CodesVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more 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
VBA Byval -Example 1

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.

VBA Byval -Example 1.1

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

VBA Byval -Example 1.2

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

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

VBA Byval -Example 1.3

Even now, the value of “k” is still zero.

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

Example 1.4

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.

Example 1.5

#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.

Example 1.6

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

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

Example 1.7

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 VBAMessage Box In VBAVBA MsgBox function is an output function which displays the generalized message provided by the developer. This statement has no arguments and the personalized messages in this function are written under the double quotes while for the values the variable reference is provided.read more.

VBA Byval Output 1

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
Example 2.1
  • 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.

VBA Byval Output 2

Things to Remember

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.

This article has been a guide to VBA Byval. Here, we discuss using the ByVal argument in VBA along with the examples and downloadable Excel template. You can learn more about VBA from the following articles: –