Afrina Nafisa
In this article, we will learn how to execute Excel VBA sub to return value using the ByRef keyword in different ways.VBA is a programming language that stands for Visual Basic Analysis. That simplifies and automates Excel work for regular Excel users. Sometimes we need to get return values from the VBA sub-procedure to execute any program but usually, subprocedure doesn’t return value directly. The solution to this problem is using the ByRef keyword to get the return value. Video 1
In the overview image, we to getting the output after using the By Ref keyword to get the return value. You will get more information once you go through the total article. So, let’s get started.
Download Practice Workbook
You can find the practice sheet here.
Excel VBA Sub to Return Value: 3 Simple Ways
The dataset below represents the P&L Sheets of the Electronics Company for 2022. This dataset contains Company Name, Expected Turn over, and Realized Turn over of the following companies to get the result if those companies are profitable or not. The below example shows you simple ways to execute Excel VBA Sub Return Value.
1. Excel VBA Sub Return Value: Getting Value as Range
In Excel, Sub Procedure does not return values like Function Procedure. Sub Procedure is basically used for executing any action. But in this case, we will get a return value from a Sub Procedure using the ByRef parameter. You can use this parameter in different ways. Here we will use the ByRef parameter as a Range. Now, follow the below steps to get the return value from the Sub procedure.
📌 Steps:
- Initially, Calculate the profit of the companies in column E using the Excel VBA code.
- After that, place the cursor in Sheet Tab and right-click on the mouse to get the options menu.
- Now, select View Code to get the Module window.
- Now write down the code in the module window to get the profit.
Code:
Sub Profit(ByRef rng As Range)
Dim cell As Range
For Each cell In rng
cell.Offset(0, 2).Value = cell.Offset(0, 1).Value - cell.Value
Next cell
End Sub
Sub Main()
Dim dataRange As Range
Set dataRange = Range("C5:C12")
Profit dataRange
MsgBox "Returns value in column E."
End SubCode Breakdown:
Sub Profit(ByRef rng As Range)
Dim cell As Range- Initially, declare the subroutine with a parameter using the ByRef keyword as a range.
For Each cell In rng
cell.Offset(0, 2).Value = cell.Offset(0, 1).Value - cell.Value
Next cell
End Sub- Now, the For loop is used here and the Offset function is used to subtract the value of the current cell from the value of the cell located two columns to the right of it.
Sub Main()
Dim dataRange As Range
Set dataRange = Range("C5:C12")
Profit dataRange- Now, declare the main Sub as Range and select the range as C5:C12. So the data can subtract the value from two columns from the right.
MsgBox "Returns value in column E."
End Sub- Lastly, write down the output note in MsgBox and end the sub.
After applying the code press the Run button and execute this process. But using the Run button every time you execute this process is a hassle and double work. In that case. Use can use Button to simplify this method.
- First, select Developer >> Insert >> Button (Icon) from the Toolbar.
- Afterward, place the button and right-click on the mouse to get the options bar.
- Therefore select Assign Macro from the option bar to assign the macro in the button.
- Consequently, an Assign Macro dialog box will open.
- Now, select the macro name Module 1. Main from the Macro Name box and click OK.
- Now, select Edit Text from the option bar as below.
- After editing the text as range, edit the formatting to complete the button.
- Lastly, click the button and the output will be similar as below.
2. Getting Return Value as String
In this method, We will get the return value from the Sub procedure using the ByRef keyword as a string. The process of this method will be quite similar to the previous method. So, follow the below steps to execute this process.
📌 Steps:
- In the beginning, go to View Code from the Sheet Tab.
- Write down the code to get the output of companies that are profited or not.
Code:
Sub CheckProfit(ByRef status As String)
Dim ws As Worksheet
Dim lastRow As Long
Dim cell As Range
Set ws = ThisWorkbook.Worksheets("String")
lastRow = ws.Cells(ws.Rows.Count, "E").End(xlUp).Row
For Each cell In ws.Range("E5:E" & lastRow)
If cell.Value > 0 Then
status = "Profited"
cell.Offset(0, 1).Value = "Profited"
Else
cell.Offset(0, 1).ClearContents
End If
Next cell
End Sub
Sub Main()
Dim profitStatus As String
profitStatus = "(E5:E12)"
CheckProfit profitStatus
MsgBox "Returns the value in column F: " & profitStatus
End SubCode Breakdown:
Sub CheckProfit(ByRef status As String)
Dim ws As Worksheet
Dim lastRow As Long
Dim cell As Range- The subroutine “CheckProfit” is defined with a parameter which is a string type and the ByRef keyword defines any modification made to the variable inside the subroutine will affect the main argument.
Set ws = ThisWorkbook.Worksheets("String")
lastRow = ws.Cells(ws.Rows.Count, "E").End(xlUp).Row- Now select the workbook and write down the workbook name and the output column of the workbook.
For Each cell In ws.Range("E5:E" & lastRow)
If cell.Value > 0 Then
status = "Profited"
cell.Offset(0, 1).Value = "Profited"
Else
cell.Offset(0, 1).ClearContents
End If
Next cell
End Sub- Now use a For loop which performs a conditional check using the OFFSET function that calculates if the companies are profited and put the output in one column right from the cell.
Sub Main()
Dim profitStatus As String
profitStatus = "(E5:E12)"
CheckProfit profitStatus- Now declare the Main Sub as String and declare the range to execute this process.
MsgBox "Returns the value in column F: " & profitStatus
End Sub- Lastly, write down the output message in msgbox to end the sub.
- After implementing the code, add a button using the process already shown in the previous method.
- Finally, the output will be similar as below.
3. Getting Sub Return Value as Array
Here, we will get the return value from the Sub procedure with the ByRef keyword as an array. In this method, we will get the valuation of the companies by multiplying the realized turnover by 5. As multiplying each and every turnover is time-consuming work we will apply the by ref keyword as an array to get the output easily. Now, follow the below steps to execute this process.
📌 Steps
- First, get the View Code option from the Sheet Tab as before.
- Therefore, write down the code in the module window as below.
Code:
Sub ModifyArray(ByRef arr() As Variant)
Dim i As Long
For i = LBound(arr) To UBound(arr)
arr(i) = arr(i) * 5
Next i
End Sub
Sub Main()
Dim myArray(1 To 8) As Variant
myArray(1) = 31
myArray(2) = 35
myArray(3) = 33
myArray(4) = 32
myArray(5) = 35
myArray(6) = 35.75
myArray(7) = 32
myArray(8) = 36.75
ModifyArray myArray
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Array")
Dim i As Long
For i = LBound(myArray) To UBound(myArray)
ws.Cells(i + 4, "E").Value = myArray(i)
Next i
MsgBox "Value Returns in column E."
End SubCode Breakdown:
Sub ModifyArray(ByRef arr() As Variant)
Dim i As Long
For i = LBound(arr) To UBound(arr)
arr(i) = arr(i) * 5
Next i
End Sub- Initially, declare the array with the ByRef keyword within a sub. This keyword indicates that what will be modified here will affect the other subroutine. I is declared as a long integer here.
Sub Main()
Dim myArray(1 To 8) As Variant
myArray(1) = 31
myArray(2) = 35
myArray(3) = 33
myArray(4) = 32
myArray(5) = 35
myArray(6) = 35.75
myArray(7) = 32
myArray(8) = 36.75
ModifyArray myArray
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Array")- Here, all the 8 My array is declared as a variant and it will give you an output multiplied by 5 to its actual value.
Dim i As Long
For i = LBound(myArray) To UBound(myArray)
ws.Cells(i + 4, "E").Value = myArray(i)
Next i- This code loops through the array and assigns each element to a cell in column “E” of the worksheet, starting from a specific row. Here, the cell is (i+4). Which is E5.
MsgBox "Value Returns in column E."
End Sub- Finally, write down the output message and end the sub.
- Finally, add a button and assign a macro in the button to simplify the procedure.
- The final output will be similar as below.
Sub vs Function VBA
There are some differences between Sub Procedure and Function Procedure. The key differences are as follows.
- Usually, the Sub procedure doesn’t return any value. Sub-procedure performs a task. On the other hand, the Function returns the value of the performed task and gives an output.
- You can not use Sub-procedure directly to execute any method. But you can use the Function directly to execute any output.
- You can recall a sub from anywhere in a program multiple times and in multiple ways. Excel users can also execute sub. On the other hand, Excel users can not execute any function and also just cannot recall a single function multiple times.
Things to Remember
- Sub procedure does not directly return any value. Sub usually helps to perform a program.
- If you pass a parameter by the ByRef keyword that means it is referred to another sub in a program.
Conclusion
In this article, we learned how to execute Excel VBA sub to return value. As Sub procedure doesn’t return value or execute any process, here we apply the ByRef keyword to return value in different ways. We have covered all the processes to solve this issue. Hopefully, you can solve the problem shown in this article. Please let us know in the comment section if there is any query or suggestions or you can also visit Exceldemy to explore more.
