WallStreetMojo

WallStreetMojo

WallStreetMojo

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

VBA ByRef

Excel VBA ByRef Function Argument

ByRef in VBA is a function called as by reference where we provide a reference to any arguments in our code, when we make custom functions and we want to use the value of any variable which is defined earlier before the function we use ByRef function, the syntax to use is simple as Function Function-Name(ByRef Variable as Data Type).

By using Byref, we can point to the original variable value without altering the variable value. This is like passing the variable value directly to the VBA subprocedure or VBA function.

vba-byref

How to Pass Argument using VBA ByRef Argument?

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

Example #1

For example, look at the below VBA code.

Code1:

Sub Procedure1()

Dim k As Integer

k = 50

Procedure2 k

MsgBox k

End Sub

vba byref example1.1

Code2:

Sub Procedure2(ByRef k As Integer)

k = k + 10

End Sub

vba byref example1.2

In the first procedure, I have declared the variable “k” as Integer.

Then I have assigned the value to this variable as 50.

After that, I have added a new line i.e.

Procedure2 k

This is the second procedure name. In this procedure, I have declared the variable within the parenthesis as String in VBA, but I have used the word “ByRef.”

ByRef k As Integer

Here I have assigned the value of the variable “k” as

k = k + 10

Ok, now I will run the code step by step by pressing the F8 key.

vba byref example1.3

vba byref example1.4

Press the F8 key two more times and place a cursor on variable “k” to see the value of the variable “k.”

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course

vba byref example1.5

Since we have assigned the value as 50, it is showing the value as 50. Now it has highlighted the line Procedure2 k, which is the second procedure name.

If I press the F8 key now, it will jump out of the current procedure and goes to the second procedure.

vba byref example1.6

Now since we have used ByRef word, it carried the variable “k” value from the above procedure.

vba byref example1.7

Press the F8 key twice. It will go back to the previous subprocedure. If you notice in the second procedure, I have applied the formula as k = k + 10. i.e., “k” value is 50 then adds 10 more to that i.e., 60 in total.

example1.8

Now the code is running in the first procedure, and in this procedure, the variable “k” value is 50. But press the F8 key and see the result in a message box.

example1.9

We got the result as 60 instead of the default value of 50 in this procedure.

The reason why we got 60 because, in the second procedure, we have applied “ByRef “so it carried the equation result (k = k + 10) to the current procedure.

Here first variable “k” value is 50, and in the second procedure variable “k” value is k + 10 i.e., 60, which is carried to the first procedure.

In the first procedure original value of the variable “k” was 50, so By Ref has changed the original value from 50 to 60 by executing the equation k = k + 10 i.e., k = 50 +10 = 60.

Example #2

Now take a look at one more example.

Code 1:

Sub P1()

Dim MyNumber As Long

MyNumber = 1

Call Change_ByRef(MyNumber)
' MyNumber is changed by the Change_ByRef procedure
MsgBox "My Number is now: " & MyNumber

End Sub

vba byref example 2.1
Code 2:

Sub Change_ByRef(ByRef NewNumber As Long)

NewNumber = 14

End Sub

example2.2

This works exactly the same as the previous code.

Initially, the value of the variable “MyNumber” is 1. Then we call the below procedure by its name

Call Change_ByRef(MyNumber)

In that procedure, the value of the variable is 14.

So, when it goes back to the previous procedure, it will assign the new value to the variable as 14.

example2.3

Recommended Articles

This has been a guide to VBA ByRef. Here we discuss how to pass an argument using VBA ByRef function along with examples and a downloadable excel template. You can learn more about VBA from the following articles –

  • VBA Exit Sub
  • Excel VBA Operators
  • Integer Data Type in VBA
  • VBA Loop
0 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ 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 ByRef Excel Template

New Year Offer - VBA Training Course (6 courses, 35+ hours video) View More