Trending September 2023 # How To Use Byval Statement In Vba Excel? # Suggested October 2023 # Top 15 Popular

You are reading the article How To Use Byval Statement In Vba Excel? updated in September 2023 on the website Nhahang12h.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested October 2023 How To Use Byval Statement In Vba Excel?

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 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

As Integer

End Sub

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

Code:

Sub

VBA_ByVal()

Dim

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

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

As Integer

A = 10 MsgBox A

End Sub

Sub

Val_Section(

ByVal

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

As Integer

A = 10 MsgBox A

End Sub

Sub

Val_Section(

ByVal

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 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

As Integer

A = 10 MsgBox A Val_Section A

End Sub

Sub

Val_Section(

ByVal

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 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.

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

As Integer

A = 10 MsgBox A

End Sub

Code:

Sub

VBA_Byval2()

Dim

As Integer

A = 10 MsgBox A

End Sub

Sub

Val_Section(

ByVal

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

As Integer

A = 10 MsgBox A

End Sub

Sub

Val_Section(

ByVal

As Integer

) A = 15

End Sub

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

As Integer

A = 10 MsgBox A

Call

Val_Section(A)

End Sub

Sub

Val_Section(

ByVal

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 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.