How to Return Values from a Sub in Excel VBA (4 Methods)

In Excel VBA, a Sub procedure is one of the most common ways to automate tasks. A Sub can perform actions. For instance, calculating results, updating cells, or formatting results. However, it does not directly return a value like a Function. That’s why it often creates confusion, especially among beginners, when they’re working with Subs and want output.

That’s why in this article, we will walk you through four effective methods to return values from a Sub in Excel VBA. It includes using ByRef parameters, multiple ByRef variables, global variables, or converting the Sub into a Function.

' Function to calculate bonus
Function CalculateBonusFunc(Sales As Double, BonusPct As Double) As Double
    CalculateBonusFunc = Sales * (BonusPct / 100)
End Function
' Sub to apply the function to the dataset
Sub ApplyBonusFunction()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change to your sheet name
    ' Add header for Bonus Amount
    ws.Cells(1, 5).Value = "Bonus Amount"
    ' Find the last row of data
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    ' Loop through each employee and calculate bonus
    For i = 2 To lastRow
        ws.Cells(i, 5).Value = CalculateBonusFunc(ws.Cells(i, 3).Value, ws.Cells(i, 4).Value)
    Next i
    MsgBox "Bonus calculation complete using Function!"
End Sub 

What Does It Mean by Returning Value from Sub in Excel VBA?

In simple words, a Sub return value means getting output from a Sub procedure. Normally, a Sub only performs actions and ends. It does not return a value directly. You can run a Sub to calculate something or perform tasks. However, it cannot send a value back to the place where it was called. So, ultimately Sub return value essentially means using different ways to get data out of a Sub.

In the following dataset, we have a list of employee sales records. Now, let’s say we want to calculate the bonus of the employees based on the sales. If we use a Sub, it will perform the calculation, but won’t directly give us a result back. Now we will return values from a Sub in Excel VBA using different methods.

It is a method that returns a value from a Sub in Excel by passing a variable ByRef  (by reference). It allows the Sub to calculate a value and directly update the variable you pass.

That means it takes the values, performs the action inside the Sub, and allows a Sub to “return” a value by changing the value of a variable passed to it. Here, you don’t use a Function. Instead, the Sub updates the variable directly.

In the following example, we will calculate each employee’s bonus by using a Sub that takes their Sales and Bonus%, and then updates a variable ByRef. We have added a new column to the worksheet, as the result will be shown directly in it.

Steps:

➤ Press  Alt  +  F11  to open the VBA editor.
➤ In the Project Explorer, right-click on your sheet >> Insert >> Module.

➤ In the new module, insert the following VBA code.

' Sub to calculate bonus 
Sub CalculateBonus(ByVal Sales As Double, ByVal BonusPct As Double, ByRef BonusAmount As Double)
    ' Calculate bonus
    BonusAmount = Sales * (BonusPct / 100)
End Sub
' Sub to apply bonus to the dataset
Sub ApplyBonusToSheet()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim empBonus As Double
    Set ws = ThisWorkbook.Sheets("Sheet1")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    ' Add header for Bonus Amount
    ws.Cells(1, 5).Value = "Bonus Amount"
    ' Loop through each row
    For i = 2 To lastRow
        Call CalculateBonus(ws.Cells(i, 3).Value, ws.Cells(i, 4).Value, empBonus)
        ws.Cells(i, 5).Value = empBonus
    Next i
End Sub

➤ Press  F5  while in ApplyBonusToSheet or run it from Excel.
➤ The code will fill Column E with calculated bonuses.

This method lets you pass multiple variables ByRef. The Sub can calculate more than one result at the same time and update all the variables directly. This is useful when you want to perform multiple calculations at a time.

For example, in our Employee Sales and Bonus dataset, we will calculate both Bonus Amount and Total Pay for each employee using multiple byref.

To return the Sub value using multiple byref parameters, in the following dataset, we have added two columns, Column E for Bonus Amount and Column F for Total Pay.

Steps:

➤ Open the VBA editor.
➤ Insert a new module.
➤ In the new module, insert the following VBA code.

' Sub to calculate multiple values
Sub CalculateBonusAndTotal(Sales As Double, BonusPct As Double, _
                           ByRef BonusAmount As Double, ByRef TotalPay As Double)
    ' Calculate bonus
    BonusAmount = Sales * (BonusPct / 100)
    ' Calculate total pay
    TotalPay = Sales + BonusAmount
End Sub
' Sub to apply calculations to the dataset
Sub ApplyBonusAndTotalToSheet()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim empBonus As Double
    Dim empTotalPay As Double
    Set ws = ThisWorkbook.Sheets("Sheet1")
    ' Find the last row with data
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    ' Add headers for new columns
    ws.Cells(1, 5).Value = "Bonus Amount"
    ws.Cells(1, 6).Value = "Total Pay"
    ' Loop through each employee
    For i = 2 To lastRow
        Call CalculateBonusAndTotal(ws.Cells(i, 3).Value, ws.Cells(i, 4).Value, _
                                    empBonus, empTotalPay)
        ws.Cells(i, 5).Value = empBonus
        ws.Cells(i, 6).Value = empTotalPay
    Next i
End Sub

➤ Run the code.
➤ The code will automatically fill Bonus Amount in Column E and Total Pay in Column F.

In this method, you keep the variables outside the Sub. Because of that, the Sub can use the variables and update them. Here, you can declare the variable at a global level to use it throughout your project modules.

We can return the Sub value using global or module-level variables in the same dataset by declaring shared variables at the top of the module and letting the Sub use those variables throughout the module.

Steps:

➤ In the new module, copy and paste the following VBA code.
➤ Run the code.

' --- Global variables to store Sales, Bonus%, and Bonus Amount ---
Dim gSales As Double
Dim gBonusPct As Double
Dim gBonusAmount As Double
' --- Sub to calculate bonus using the global variables ---
Sub CalculateBonus()
    ' Use the global variables directly
    gBonusAmount = gSales * (gBonusPct / 100)
End Sub
' --- Sub to apply the calculation to the dataset ---
Sub RunExample()
    Dim ws As Worksheet
    Dim lastRow As Long, i As Long
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change to your sheet name
    ' Add header for Bonus Amount
    ws.Cells(1, 5).Value = "Bonus Amount"
    ' Find last row of data
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    ' Loop through each employee
    For i = 2 To lastRow
        ' Load Sales and Bonus% into Global variables
        gSales = ws.Cells(i, 3).Value
        gBonusPct = ws.Cells(i, 4).Value
        ' Call Sub to calculate bonus
        Call CalculateBonus
        ' Write result back to column E (Bonus Amount)
        ws.Cells(i, 5).Value = gBonusAmount
    Next i
    MsgBox "Bonus calculation complete using Global Variables!"
End Sub

This method lets you return a value by converting your Sub into a Function. Here, the Function takes the input, performs the calculation, and sends it back directly. Unlike a Sub, it gives you a clear output that you can use in your worksheet or other procedures.

We can return a Sub value by converting it into a Function. For each employee, the Function calculates the bonus and directly writes it into the Bonus Amount column.

Steps:

➤ In the new module, insert the following VBA code.
➤ Run the code.

' Function to calculate bonus
Function CalculateBonusFunc(Sales As Double, BonusPct As Double) As Double
    CalculateBonusFunc = Sales * (BonusPct / 100)
End Function
' Sub to apply the function to the dataset
Sub ApplyBonusFunction()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change to your sheet name
    ' Add header for Bonus Amount
    ws.Cells(1, 5).Value = "Bonus Amount"
    ' Find last row of data
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    ' Loop through each employee and calculate bonus
    For i = 2 To lastRow
        ws.Cells(i, 5).Value = CalculateBonusFunc(ws.Cells(i, 3).Value, ws.Cells(i, 4).Value)
    Next i
    MsgBox "Bonus calculation complete using Function!"
End Sub

➤ After completing the calculation, it shows a confirmation message.

➤ The VBA code writes the returned value into column E (Bonus Amount).

Frequently Asked Questions

How to use sub in Excel VBA?

In Excel, you can use Sub to perform different actions. For instance, calculating results, updating cell values, formatting worksheets, automating tasks, and showing messages. It can’t return a value but can call other procedures.

How to return a value from a Function in VBA?

You can return a value from a Function in VBA by assigning the result to the name of the Function. For example,

Function GetBonus(Sales As Double, BonusPct As Double) As Double
    GetBonus = Sales * (BonusPct / 100)
End Function

Here, Function takes Sales and Bonus% as inputs, multiplies Sales by Bonus%, and assigns the result to GetBonus.

How to return a cell value in VBA?

You can return a cell value in VBA by reading it with the Range or Cells property. For example,

Sub GetCellValue()
    Dim value As Variant
    value = Range("A1").Value
    MsgBox value
End Sub

Here, the code gets the value from cell A1 and shows it in a message box.

Wrapping Up

In this quick tutorial, we have learnt how Excel VBA returns a value using four different effective methods. Feel free to download our sample workbook and try these methods yourself. Let us know how these VBA solutions have made your tasks easier and your automation more efficient.