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.

How to Use ByVal Statement in VBA Excel?

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

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.

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

Code:

Sub VBA_ByVal()

End Sub

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

Code:

Sub VBA_ByVal()

Dim A As Integer

End Sub

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

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

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

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

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.

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.

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.

Step 11: Now to call the value from below written sub procedure to the main procedure, use Val_Section which is our 2^nd 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

Step 12: Now if again compile the code using the F8 key and we will notice once the compiler reaches End Sub of 2^nd 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.

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

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

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

Step 4: Now to call the value mentioned in 2^nd sub procedure, we will use word CALL with the name of 2^nd 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

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.

Continuing the compiling, as we reach at value 15 of variable A from the 2^nd sub procedure, then hovering the cursor will reflect the value as 10 which is the same as 1^st 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?