• Skip to primary navigation
  • Skip to content
  • Skip to primary sidebar
  • Skip to footer
EDUCBA

EDUCBA

MENUMENU
  • Resources
        • Excel Charts

          • Histogram Chart Excel
          • Basic Excel Formulas
          • Text to Columns in Excel
        • Excel Charts
        • Excel Tips

          • Excel Gantt Chart
          • IFERROR with VLOOKUP
          • Data Table in Excel
        • Excel Tips
        • Excel Tools in Excel

          • Stacked Column Chart
          • Cheat Sheet of Excel Formulas
          • Excel Data Validation
        • Histogram chart in excel
        • Others

          • Resources (A-Z)
          • Excel Functions
          • Financial Functions in Excel
          • Logical Functions in Excel
          • Lookup Reference Functions in Excel
          • Maths Function in Excel
          • TEXT and String Functions in Excel
          • View All
  • Free Courses
  • All Courses
        • Certification Courses

          Excel Course 1
        • All in One Bundle

          All-in-One-Excel-VBA-Bundle
        • Excel course

          Excel-Training
        • Others

          • Excel advanced course
          • VBA Course
          • Excel Data Analysis Course
          • Excel for Marketing Course
          • Excel for Finance Course
          • View All
  • 120+ Courses All in One Bundle
  • Login

VBA ByRef

Home » Excel » Blog » VBA » VBA ByRef

VBA ByRef

What is ByRef in VBA?

Byref in VBA stands for “By Reference”. With the help of VBA Byref, we can target the original value without changing the value stored in variables. In other words, we will directly be passing the value to Sub procedures instead of going through the regular methods of defining and assigning the values to variables.

In VBA ByRef, we define the sub-procedure after we set the rule for ByRef. This could be done below the sub-procedure where we want to write the code. In ByRef, we redefine the variable which is used in Sub procedure. And it only works properly when we call the ByRef condition in our subprocedure.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

How to Use the ByRef Function in Excel VBA?

Below are the different examples to use ByRef Function in Excel using VBA Code.

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

Excel VBA ByRef – Example #1

First, let us learn how to insert a ByRef in VBA, for this follow the below steps. In this example, we will see how to use VBA ByRef for a simple mathematical subtraction work. For this, we would need a module.

Step 1: So, go to VBA and open a module from the Insert menu option as shown below.

VBA Module ByRef

Step 2: In the newly opened module, write the subcategory of VBA ByRef as shown below.

Popular Course in this category
Cyber Week Sale
VBA Training (3 Courses, 12+ Projects) 3 Online Courses | 13 Hands-on Projects | 45+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.7 (2,656 ratings)
Course Price

View Course

Related Courses
Excel Advanced Training (14 Courses, 21+ Projects)Excel Data Analysis Training (12 Courses, 6+ Projects)

Code:

Sub VBA_ByRef1()

End Sub

VBA ByRef Example 1.1

Step 3: Now define a variable let’s say it is an A as Integer.

Code:

Sub VBA_ByRef1()

Dim A As Integer

End Sub

VBA ByRef Example 1.2

Step 4: Give any number to variable A. Let that number be 1000.

Code:

Sub VBA_ByRef1()

Dim A As Integer
A = 1000

End Sub

VBA ByRef Example 1.3

Step 5: To print the value stored in variable A, we would use Msgbox.

Code:

Sub VBA_ByRef1()

Dim A As Integer
A = 1000
MsgBox A

End Sub

VBA ByRef Example 1.4

Step 6: Now we compile and run this code by clicking on the Play button as shown below. We will get a message box with the value stored in variable A as 1000.

VBA ByRef

Now apply VBA ByRef, create another sub-category below the first one and assign the defined variable from the first subcategory with ByRef.

Step 7: By this, we will allow the second subcategory to use the values stored in variable A.

Code:

Sub VBA_ByRef1()

Dim A As Integer
A = 1000
MsgBox A

End Sub
Sub VBA_ByRef2(ByRef A As Integer)

End Sub

Example 1.5

Step 8: Now call the variable A here again and subtract any value from variable A, to get the output value in the same variable. Let’s subtract 100 from the value of variable A so that we would get a measurable number.

Code:

Sub VBA_ByRef1()

Dim A As Integer
A = 1000
MsgBox A

End Sub
Sub VBA_ByRef2(ByRef A As Integer)

A = A - 100

End Sub

Example 1.6

Step 9: Now if we compile each step of the code, we will notice that when the cursor reached variable A, we will see it has only 0 stored in it.

VBA ByRef Example 1.7

Step 10: When the cursor reached End Sub, the output we will get as 1000 in the message box.

Example 1.8

Step 11: It is because we haven’t assigned the ByRef to the first subcategory. Now we will assign subcategory name before the message box function of the first subcategory and see what will happen.

Code:

Sub VBA_ByRef1()

Dim A As Integer
A = 1000

VBA_ByRef2 A

MsgBox A

End Sub
Sub VBA_ByRef2(ByRef A As Integer)

A = A - 100

End Sub

Example 1.9

Step 12: And now, run the complete code again. We will see, the second value which is stored in variable A as 100 got subtracted from the first value 1000. As a result, we got the output message as 900.

VBA ByRef 1

Step 13: This is the main advantage of using ByRef. We don’t need to define multiple variables for a single job. Just one variable is enough to perform the whole task in different ways. We can use more than one ByRef in a single module.

To justify, what we have understood, let’s add another ByRef in the same Module.

Code:

Sub VBA_ByRef1()

Dim A As Integer
A = 1000

VBA_ByRef2 A

MsgBox A

End Sub
Sub VBA_ByRef2(ByRef A As Integer)

A = A - 100

End Sub
Sub VBA_ByRef3(ByRef A As Integer)

End Sub

VBA ByRef Example 1.11

Step 14: In this subcategory, let’s use multiplication.

Code:

Sub VBA_ByRef1()

Dim A As Integer
A = 1000

VBA_ByRef2 A

MsgBox A

End Sub
Sub VBA_ByRef2(ByRef A As Integer)

A = A - 100

End Sub
Sub VBA_ByRef3(ByRef A As Integer)

A = A * 2

End Sub

Example 1.12

Step 15: Again compile and run the code again. We will see that value obtained from the above steps as 900 is now multiplied by 2 to get 1800 as output.

VBA ByRef Example 1.0

Excel VBA ByRef – Example #2

In this example, we will see, how ByRef works with other kind of integers.

Step 1: Open a module and write the subcategory as shown below.

Code:

Sub VBA_ByRef4()

End Sub

VBA ByRef Example 2.1

Step 2: Now define a variable A as Double. This will allow us to use decimal values.

Code:

Sub VBA_ByRef4()

Dim A As Double

End Sub

VBA ByRef Example 2.2

Step 3: Assign any decimal value to variable A.

Code:

Sub VBA_ByRef4()

Dim A As Double
A = 1.23

End Sub

Example 2.3

Step 4: Now again use the message box to see the value stored in variable A.

Code:

Sub VBA_ByRef4()

Dim A As Double
A = 1.23

MsgBox A

End Sub

VBA ByRef Example 2.5

Now if we run the code, we would get 1.23 as output.

Step 5: In a different manner, we will use Function to define ByRef as Double with variable A.

Code:

Sub VBA_ByRef4()

Dim A As Double
A = 1.23

MsgBox A

End Sub
Function AddTwo(ByRef A As Double) As Double

End Function

Example 2.6

Step 6: Now add any number to variable A. Let’s say it is 10.

Code:

Sub VBA_ByRef4()

Dim A As Double
A = 1.23

MsgBox A

End Sub
Function AddTwo(ByRef A As Double) As Double

A = A + 10

End Function

Example 2.7

Step 7: And again use this defined ByRef function in the first subcategory. Here we will be seeing two message box, one for variable A and other for ByRef.

Code:

Sub VBA_ByRef4()

Dim A As Double
A = 1.23

MsgBox AddTwo(A)
MsgBox A

End Sub
Function AddTwo(ByRef A As Double) As Double

A = A + 10

End Function

Example 2.8

Step 8: Same would be reflected in the message box as well.

VBA ByRef Example 2.9

Step 9: And in the next run it will give the added value of 10 into the original variable value of 1.23 as shown below.

VBA ByRef Example 2.10

This is how VBA Byref takes the reference of the value defined once and then populate the output as per the new condition.

Pros and Cons of VBA ByRef

  • When writing big codes, it saves a lot of time by considering the already defined variable, so that its value can be used again and again.
  • We don’t have to define many variables as per formula we want to apply.
  • We can apply many ByRef conditions in a single module without even disturbing the process.
  • We cannot use VBA Byref in complex code structure.

Things to Remember

  • When considering more than one ByRef conditions, the output will be based on the last sub procedure ByRef we defined, but it also considers all the ByRef conditions used previously.
  • Final output will have sequential processed output. Not only the latest one.
  • This process cannot be done by recording the macro.
  • We can see the value stored in each stage of the variable by compiling the code.
  • Once done, save the excel file as Macro Enabled excel format, so that we will not lose code in future.

Recommended Articles

This is a guide to VBA ByRef. Here we discuss how to use ByRef function in Excel using VBA code along with practical examples and downloadable excel template. You may also look at the following articles to learn more –

  1. Guide to VBA UBound Function
  2. OFFSET Excel Function (Example, Uses)
  3. Create Hyperlink in Excel VBA
  4. How to Use Excel Fill Handle?

All in One Excel VBA Bundle (120+ Courses, 30+ Projects)

120+ Online Courses

30+ Projects

500+ Hours

Verifiable Certificates

Lifetime Access

Learn More

0 Shares
Share
Tweet
Share
Reader Interactions
Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar
Excel Functions Tutorials
  • VBA
    • VBA Max
    • VBA Environ
    • VBA Not
    • VBA LOOKUP
    • Programming in Excel
    • VBA Name Worksheet
    • VBA ScreenUpdating
    • VBA Do Loop
    • VBA Declare Array
    • VBA Macros
    • VBA Delete Sheet
    • VBA Editor
    • VBA Error Handling
    • VBA Goal Seek
    • VBA DateDiff
    • VBA On Error Resume Next
    • VBA Examples
    • VBA Counter
    • VBA Val
    • VBA Me
    • VBA Double
    • VBA Variable Types
    • VBA FreeFile
    • VBA Switch Case
    • VBA Unprotect Sheet
    • VBA Format Number
    • VBA ReDim
    • VBA IF Statements
    • VBA DoEvents
    • VBA Public Variable
    • VBA Columns
    • VBA Cdate
    • VBA Selection Range
    • VBA Hide Columns
    • VBA COUNTA
    • VBA Insert Row
    • VBA Protect Sheet
    • VBA AutoFill
    • VBA IF Not
    • VBA GetObject
    • VBA Debug Print
    • VBA Collection
    • VBA Text
    • VBA Randomize
    • VBA Variant
    • VBA Selection
    • VBA Right
    • VBA Date Format
    • VBA Rename Sheet
    • VBA Paste Values
    • VBA InputBox
    • VBA Conditional Formatting
    • VBA Pause
    • VBA Pivot Table
    • VBA Message Box
    • VBA OFFSET
    • VBA ByRef
    • FileCopy in VBA
    • VBA StrConv
    • VBA SUB
    • VBA Boolean
    • VBA Global Variables
    • VBA Worksheets
    • VBA IIF
    • VBA Close UserForm
    • VBA Variable Declaration
    • VBA Join
    • VBA Dictionary
    • VBA Operators
    • VBA Charts
    • VBA For Each Loop
    • VBA FileDialog
    • VBA Format
    • VBA MID
    • VBA GetOpenFileName
    • VBA DatePart
    • VBA Code
    • VBA Option Explicit
    • VBA FileSystemObject (FSO)
    • VBA ASC
    • VBA UserForm
    • VBA Cells
    • VBA InStrRev
    • VBA Class Module
    • VBA Constants
    • VBA Length of String
    • VBA DateSerial
    • VBA StrComp
    • VBA Array Length
    • VBA Check File Exists
    • VBA Ubound
    • VBA CDBL
    • VBA Activate Sheet
    • VBA DateValue
    • VBA Borders
    • VBA Comment
    • VBA Intersect
    • VBA Workbook
    • VBA Concatenate
    • VBA Name
    • VBA Find and Replace
    • VBA Tutorial
    • VBA CSTR
    • VBA Save As
    • VBA Hyperlink
    • VBA Print
    • VBA Switch
    • VBA END
    • VBA Like
    • VBA Set
    • VBA excel programming
    • VBA Call Sub
    • VBA Object
    • VBA RoundUp
    • VBA ArrayList
    • VBA Named Range
    • VBA PowerPoint
    • VBA Block Comment
    • VBA OverFlow Error
    • VBA Insert Column
    • VBA Lcase
    • VBA List Box
    • VBA Delete File
    • VBA Clear Contents
    • VBA TextBox
    • VBA Font Color
    • VBA Range Cells
    • VBA INT
    • VBA UCASE
    • VBA Value
    • VBA Remove Duplicates
    • VBA Break for Loop
    • VBA Sleep
    • VBA Do Until Loop
    • VBA Union
    • VBA Long
    • VBA Copy Paste
    • VBA Data Types
    • VBA Delete Column
    • VBA Enum
    • VBA IsEmpty
    • VBA 1004 Error
    • VBA RegEx
    • VBA IsNumeric
    • VBA Paste
    • VBA Transpose
    • VBA Left
    • VBA Delete Row
    • VBA Integer
    • VBA Active Cell
    • VBA InStr
    • VBA Round
    • VBA Subscript out of Range
    • VBA Dim
    • VBA Replace
    • VBA Sort
    • VBA String
    • VBA Split
    • VBA Wait
    • VBA MOD
    • VBA Time
    • VBA TIMER
    • VBA While Loop
    • VBA Date
    • Excel VBA MsgBox
    • VBA IFError
    • VBA Color Index
    • VBA Match
    • VBA Case
    • VBA Arrays
    • VBA GoTo
    • VBA On Error
    • VBA Range
    • VBA Do While Loop
    • VBA Number Format
    • VBA Loops
    • VBA TRIM
    • VBA Find
    • VBA Select Case
    • VBA Else If
  • Excel Functions (10+)
  • Excel Tools (97+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (12+)
  • Lookup Reference Functions in Excel (30+)
  • Maths Function in Excel (39+)
  • TEXT and String Functions in Excel (25+)
  • Date and Time Function in Excel (20+)
  • Statistical Functions in Excel (55+)
  • Information Functions in Excel (4+)
  • Excel Charts (44+)
  • Excel Tips (195+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (14+)
  • Excel Formula and Functions (20+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • VBA Training
  • Excel Advanced Training
  • Excel Data Analysis Training
Footer
About Us
  • Who is EDUCBA?
  • Sign Up
  •  
Free Courses
  • Free Online Excel Course
  • Free Vba Course
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training
Resources
  • Resources (A To Z)
  • Excel Charts
  • Excel Tips
  • Excel Tools in Excel
  • Excel Functions
  • Financial Functions in Excel
  • Logical Functions in Excel
  • Lookup Reference Functions in Excel
  • Maths Function in Excel
  • TEXT and String Functions in Excel
  • Date and Time Function in Excel
  • Statistical Functions in Excel
  • Information Functions in Excel
Apps
  • iPhone & iPad
  • Android
Support
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions

© 2019 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

Download VBA ByRef Excel Template

By continuing above step, you agree to our Terms of Use and Privacy Policy.
EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

Let’s Get Started
Please provide your Email ID
Email ID is incorrect

Cyber Week Offer - All in One Excel VBA Bundle (120+ Courses, 500+ hours of Videos) View More

Cyber Week Offer - Cyber Week Offer - All in One Excel VBA Bundle (120+ Courses, 500+ hours of Videos) View More