Trending September 2023 # How To Use Variable Byref In Excel Using Vba? # Suggested October 2023 # Top 10 Popular

You are reading the article How To Use Variable Byref In Excel Using Vba? updated in September 2023 on the website Happystarlongbien.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 Variable Byref In Excel Using Vba?

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.

Watch our Demo Courses and Videos

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

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.

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

Code:

Sub

VBA_ByRef1()

End Sub

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

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

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

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

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

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.

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

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

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.

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

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

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.

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

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

Step 3: Assign any decimal value to variable A.

Code:

Sub

VBA_ByRef4()

Dim

A

As Double

A = 1.23

End Sub

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

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

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

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

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

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.

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 –

You're reading How To Use Variable Byref In Excel Using Vba?

Update the detailed information about How To Use Variable Byref In Excel Using Vba? on the Happystarlongbien.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!