WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA ByVal

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 that are called in different functions, there are certain circumstances that the value for the original argument is changed when the function is being called; the byval statement 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.

VBA-ByVal

How to use ByVal Argument in VBA?

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

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

VBA Byval -Example 1

In the above two macro procedures, we 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 the Macro2, we have reset the value of the variable 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 line by line by pressing the F8 key.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

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

VBA Byval -Example 1.1

At this point in time, place a cursor on the variable “k,” and 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 one more time, 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 and see the value of the k.

Example 1.4

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.

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. Now press the F8 key two more times.

Example 1.6

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

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

Example 1.7

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.

VBA Byval Output 1

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

Example 2.1

  • 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 the value of variable “k” as ten 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.

VBA Byval Output 2

Things to Remember

ByVal argument doesn’t affect the variable value even after running the macro, but with the 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 VBA along with the examples and downloadable excel template. You can learn more about VBA from the following articles –

  • VBA ByRef Argument Type Mismatch
  • VBA Split String into Array
  • Set Range in VBA
  • VBA Examples
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA ByVal Excel Template

New Year Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More