VBA ByVal

Excel VBA ByVal Function Argument

ByVal is a statement in VBA. ByVal stands for By Value i.e. when the subprocedure called 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 which are called in different functions there are certain circumstances that the value for the original argument is changed when the function is being called, byval statement in VBA prohibits the procedure or the code changing the value for the argument.

I know it is not easy to understand by reading the explanation but with practical examples of function, we can actually understand better.

How to use ByVal Argument in VBA?

Example #1

For example, look at the below VBA codes.

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

In the above two macro procedures, we have 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 the Macro2 we have reset the value of the variable to k = k + 5. In this macro, we have used the vba “ByVal” argument to assign the value to the variable “k”.

Now to understand “ByVal” let’s run the VBA code line by line by pressing the F8 key.

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

At this point in time place a cursor on the variable “k” and it should show the value of the variable “k”.

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

#2 – Press the F8 key one more time and it will jump to the third line.

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

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

Since the “k” value is set to 50 and code is executed and so value showing as 50.

#4 – Now the highlighted line is “Macro2 k” i.e. by pressing the F8 key it 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. Now press the F8 key two more times.

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

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

When the macro jumped back to the original procedure Macro1 our variable “k” value is no longer 55 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.

So, as we have told at the beginning of the article “ByVal” argument doesn’t carry values from one procedure to another, even though it carries the value of the variable from the first macro to second by the moment it encounters the line “ByVal” when it comes back to the original macro it resets the value to 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

• This 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 and this time the value of variable “k” is 15.

This macro carries value of variable “k” as 10 from macro “P1” to macro “P2” and in this macro, the value will be reset 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.

Things to Remember

ByVal argument doesn’t affect the variable value even after running the macro but with By Ref argument, we can carry the value of the variable from one macro to the other.

Recommended Articles

This has been a guide to VBA Byval. Here we discuss how to use the ByVal argument in excel VBA along with the examples and downloadable excel sheet. You can learn more about VBA from the following articles –

• Split String into Array
• How to Set Range in Excel VBA?
• Get Cell Value | Using Range Property
• VBA Format Date
• User-Defined Function in Excel VBA
• Split Function in VBA
• Right Function in VBA
• String Functions in VBA