EDUCBA

Home VBA VBA Resources VBA Coding VBA ByVal

VBA ByVal

Ashwani Jaiswal
Article byAshwani Jaiswal
Madhuri Thakur
Reviewed byMadhuri Thakur

VBA Byval

Excel VBA ByVal

In VBA, we have a statement called ByVal which is used for calling the value from the subprocedure when the main procedure value is down to 0 or in other words when the value is reset. Where ByVal stands for By Value, which means replacing the main value by the value of another subprocedure. Excel VBA ByVal is the reference of linking once sub procedure with others so that we can use the linked sub procedure’s value by calling it using By Val. We can also give the reference of the value using ByRef. ByVal is used when we want to call a value from another sub procedure, but we do not want to change the value of argument whereas ByRef is used when we want to change the value using subprocedure.

 

 

VBA ByVal does not have any specific syntax which we need to follow. But the correct position of ByVal in VBA code will definitely make complete sense out of it. The above explanation would be much clear using the example shown below.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

How to Use ByVal Statement in VBA Excel?

We will learn how to use a ByVal Statement in Excel by using the VBA Code.

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

Example #1

To know the values from other subprocedures, when the main procedure value is down to 0 or in other words when the value is reset. For this, follow the below steps:

Step 1: Insert a new module inside Visual Basic Editor (VBE). Click on Insert tab > select Module.

Insert Module

Step 2: Write the subprocedure in the name of the performed operations or any name.

Code:

Sub VBA_ByVal()

End Sub

VBA Byval Example 1-2

Step 3: Define a variable as Integer using DIM as shown below.

Code:

Sub VBA_ByVal()

Dim A As Integer

End Sub

VBA Byval Example 1-3

Step 4: Assign a number to defined variable A. We are choosing number 10.

Code:

Sub VBA_ByVal()

Dim A As Integer
A = 10

End Sub

VBA Byval Example 1-4

Step 5: Use the message box to see the value stored in variable A.

Code:

Sub VBA_ByVal()

Dim A As Integer
A = 10
MsgBox A

End Sub

VBA Byval Example 1-5

Step 6: Now after that, we will be writing another sub procedure, by that we will be calling another value for the same variable.

Code:

Sub VBA_ByVal()

Dim A As Integer
A = 10
MsgBox A

End Sub

Sub Val_Section(ByVal A As Integer)

End Sub

VBA Byval Example 1-6

Step 7: Then we will use the same variable A and add any number to see the change in value from the main procedure.

Code:

Sub VBA_ByVal()

Dim A As Integer
A = 10
MsgBox A

End Sub

Sub Val_Section(ByVal A As Integer)

A = A + 12

End Sub

VBA Byval Example 1-7

Step 8: Now we will compile the code by pressing the F8 function key. We will see, as the compiler reaches the variable A, hovering the cursor there will reflect the value as 0 which is the initial value.

VBA Byval Example 1-8

Step 9: And once the cursor reaches at message box, then the value will be changed to 10 as now code has compiled till that.

VBA Byval Example 1-9

Step 10: Now run the code by pressing function key F5 and to run the code, click on the Play button located below the menu bar, the message box will give us the value as 10.

Message Box Example 1-10

Step 11: Now to call the value from below written sub procedure to the main procedure, use Val_Section which is our 2nd subprocedure name with variable A.

Code:

Sub VBA_ByVal()

Dim A As Integer
A = 10
MsgBox A
Val_Section A

End Sub

Sub Val_Section(ByVal A As Integer)

A = A + 12

End Sub

Integer Example 1-11

Step 12: Now if again compile the code using the F8 key and we will notice once the compiler reaches End Sub of 2nd sub procedure, on hovering the cursor there we will see the variable A has now the summed value as 22 adding the number from both the subprocedures.

VBA Byval Example 1-12

Example #2

There is another method to call the value from other sub procedure to the main procedure. For this, follow the below steps:

Step 1: For this, we will be using the same first half of the code as shown below.

Code:

Sub VBA_Byval2()

Dim A As Integer
A = 10
MsgBox A

End Sub

VBA Byval Example 2-1

Step 2: Now continuing the code, start the subprocedure for ByVal again as Integer.

Code:

Sub VBA_Byval2()

Dim A As Integer
A = 10
MsgBox A

End Sub

Sub Val_Section(ByVal A As Integer)

End Sub

Integer Example 2-2

Step 3: Here we will choose a different value which we want to call in the same variable A as 15.

Code:

Sub VBA_Byval2()

Dim A As Integer
A = 10
MsgBox A

End Sub

Sub Val_Section(ByVal A As Integer)

A = 15

End Sub

VBA Byval Example 2-3

Step 4: Now to call the value mentioned in 2nd sub procedure, we will use word CALL with the name of 2nd sub procedure with variable A as shown below in the first part of the code.

Code:

Sub VBA_Byval2()

Dim A As Integer
A = 10
MsgBox A
Call Val_Section(A)

End Sub

Sub Val_Section(ByVal A As Integer)

A = 15

End Sub

VBA Byval Example 2-4

Step 5: Similar to the procedure shown in example-1, compile the code using F8 step by step. We will see, as the compiler compiles the first subprocedure, we will get the message box with number 10 which we have used in variable A.

Message Box Example 2-5

Continuing the compiling, as we reach at value 15 of variable A from the 2nd sub procedure, then hovering the cursor will reflect the value as 10 which is the same as 1st subprocedure.

And at last when our compiler reaches at End Sub then it will reflect the value as 15 after hovering the cursor, which is now called by using ByVal operation in the first subprocedure. But the message will only reflect the value as 10.

Pros & Cons of VBA ByVal

Below are the pros and cons of VBA ByVal:

  • It is very easy to implement the ByVal even using the simple code of storing any number.
  • We can pass any number as per the data type used with the limit range.
  • VBA ByVal doesn’t reflect the updated or called value from the other sub procedure through the message box. It carries the same value even the message box pops up multiple times.

Things to Remember

  • VBA ByVal and ByRef both are used for giving the reference. But the ByRef is only used for giving the reference whereas ByVal is used for calling the values stored in a different subprocedure.
  • Hover the cursor at the variable name only to see the current position of value. Even after the compiler passes from there, the updated value will be seen only by hovering the cursor at the exact position.
  • VBA ByVal carries the same value there in the first subprocedure once the code is run. And whatever changes happen when we call the value using ByVal, it will again reset to the previous value only.
  • Once we are done with writing the code, always remember to save the code in Macro enabled excel format which is used for saving VBA Code.

Recommended Articles

This is a guide to the VBA ByVal. Here we discuss how to use ByVal Statement in excel VBA along with practical examples and downloadable excel template. You can also go through our other suggested articles –

  1. How to Use VBA Login?
  2. VBA Month | Examples With Excel Template
  3. How to Use Create Object Function in VBA Excel?
  4. How to Use VBA IsError Function?