In Excel VBA, both Functions and Subs are used to perform tasks, but they are not the same. A Sub (short for Subroutine) carries out actions such as formatting cells, copying data, or showing messages. A Function, on the other hand, performs a calculation or process and returns a value that can be used in formulas or other VBA code.
In this tutorial, we will look at how VBA behaves differently when using a Function and a Sub. We will go through several simple examples that show how each works in real Excel situations, such as returning values, taking inputs, and interacting with worksheets. By the end, you will know when to use a Function and when a Sub is the better option.
Steps to returning a value to Excel with function vs sub in VBA
➤ Press Alt + F11 to open the VBA Editor.
➤ Go to Insert >> Module to add a new module.
➤ Paste this Function code first:
Function PerformancePercent(Sales As Double, Target As Double) As Double
PerformancePercent = (Sales / Target) * 100
End Function➤ Return to Excel and enter this formula in a cell: =PerformancePercent(C2, D2)
➤ The formula will return the performance percentage directly in the worksheet cell.
➤ Next, to compare how a Sub behaves differently, paste this Sub procedure in the same module:
Sub ShowPerformancePercent()
Dim Sales As Double
Dim Target As Double
Dim Result As Double
Sales = Range("C2").Value
Target = Range("D2").Value
Result = (Sales / Target) * 100
MsgBox "Performance: " & Result & "%"
End Sub➤ Press F5 in the VBA editor to run the Sub procedure.
➤ The Sub will display the performance percentage in a message box, but it will not return any value to the worksheet.
Returning a Value in Excel with VBA Function vs Sub
This scenario shows how a Function in VBA can return a value directly to an Excel cell, while a Sub cannot. Functions are designed to compute and give results back to Excel formulas, whereas Subs are used to perform actions like formatting or displaying messages.
This is the dataset that we will be using to demonstrate all the methods:
Using our dataset, we will calculate each employee’s Performance Percentage based on their Sales and Target. The Function will return this value, while the Sub will only show it through a message box.
Steps:
➤ Press Alt + F11 to open the VBA Editor.
➤ Go to Insert >> Module to add a new module.
➤ Paste this Function code first:
Function PerformancePercent(Sales As Double, Target As Double) As Double
PerformancePercent = (Sales / Target) * 100
End Function
➤ Now, in Excel, enter this formula in a cell:
=PerformancePercent(C2, D2)
This will return the percentage of performance directly in the cell.
➤ Next, to see how a Sub behaves differently, paste this Sub procedure in the same module:
Sub ShowPerformancePercent()
Dim Sales As Double
Dim Target As Double
Dim Result As Double
Sales = Range("C2").Value
Target = Range("D2").Value
Result = (Sales / Target) * 100
MsgBox "Performance: " & Result & "%"
End Sub 
➤ Press F5 while in the VBA editor to run the Sub.
You will see the performance percentage displayed in a message box, but it won’t return any value to a cell.
➧ The Sub performs an action (showing a message) but does not send a result back to Excel.
➧ Functions are used in worksheet formulas, while Subs are executed manually or through buttons.
Performing Actions on Data with Function vs Sub in VBA
This scenario demonstrates how a Sub procedure can modify or manipulate worksheet data directly, while a Function cannot change cell contents or formatting. In VBA, Subs are used to perform actions such as updating values, formatting cells, or highlighting data. Functions, on the other hand, are meant for returning values, not changing the worksheet itself.
Using our dataset, we’ll use a Sub to highlight employees whose Sales exceed the Target, while a Function will simply return whether the employee met the target or not.
Steps:
➤ Press Alt + F11 to open the VBA Editor.
➤ Go to Insert >> Module to add a new module.
Sub Example (Performs an Action):
Sub HighlightTopPerformers()
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
Set ws = ThisWorkbook.Sheets("Sheet1")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
For i = 2 To lastRow
If ws.Cells(i, 3).Value > ws.Cells(i, 4).Value Then
ws.Rows(i).Interior.Color = vbYellow
End If
Next i
End Sub
➤ Press F5 to run this Sub.
Rows where Sales > Target will automatically be highlighted in yellow.
Function Example (Only Returns a Result):
Function MetTarget(Sales As Double, Target As Double) As String
If Sales >= Target Then
MetTarget = "Yes"
Else
MetTarget = "No"
End If
End Function 
➤ Return to Excel and type this formula in a new column:
=MetTarget(C2, D2)
This will return “Yes” or “No” based on each employee’s performance, but it will not highlight or change any cells.
➧ The Function cannot alter cell formatting or content; it only returns a value to Excel.
➧ Use a Sub when you need to perform tasks that change your workbook, and a Function when you just need a calculated result.
Calling One Procedure from Another with Function vs Sub in VBA
This scenario shows how Subs and Functions behave differently when called from other procedures. In VBA, both can be called from another Sub or Function, but the way they are used and the purpose they serve are not the same. A Sub performs an action, while a Function returns a value that can be used in further calculations or logic.
We’ll create one Sub that calls another Sub to perform an action, and then a separate Sub that calls a Function to use its returned value.
Steps:
➤ Press Alt + F11 to open the VBA Editor.
➤ Go to Insert >> Module to add a new module.
Example 1: Calling a Sub from Another Sub
Sub MainProcedure()
MsgBox "Starting process..."
Call DisplayMessage
MsgBox "Process complete."
End Sub
Sub DisplayMessage()
MsgBox "Hello from DisplayMessage Sub!"
End Sub➤ Run MainProcedure from the VBA editor (press F5).
You’ll see two message boxes appear, one before and one after calling the second Sub.
This shows that a Sub can be called simply to perform an action, such as showing a message or updating data.
Example 2: Calling a Function from a Sub
Sub CalculateBonus()
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
Dim bonus As Double
Set ws = ThisWorkbook.Sheets("Sheet1")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
For i = 2 To lastRow
bonus = GetBonus(ws.Cells(i, 3).Value)
ws.Cells(i, 5).Value = bonus
Next i
End Sub
Function GetBonus(Sales As Double) As Double
If Sales > 5000 Then
GetBonus = Sales * 0.1
Else
GetBonus = 0
End If
End Function➤ Run CalculateBonus.
This Sub calls the Function GetBonus, which returns a calculated value that is written into column E.
➧ A Function can be called from a Sub to get a return value that can be used in calculations.
➧ Functions can be used for logical or mathematical operations, while Subs handle tasks that perform actions or update the workbook.
Using Functions vs Subs in Excel Cells
This scenario highlights a major difference between Functions and Subs in VBA: Functions can be used directly in Excel cells, but Subs cannot. Functions return a value that can appear in a worksheet cell, while Subs are meant to perform actions like modifying data, displaying messages, or formatting sheets.
Steps:
➤ Press Alt + F11 to open the VBA Editor.
➤ Go to Insert >> Module to add a new module.
Example 1: Using a Function in a Cell
Function DoubleValue(x As Double) As Double
DoubleValue = x * 2
End Function➤ Go to Sheet1, in any cell type:
=DoubleValue(100)
The cell will display 200.
This demonstrates that a Function can return a value directly into an Excel worksheet cell.
Example 2: Attempting to Use a Sub in a Cell
Sub DoubleValueSub(x As Double)
MsgBox x * 2
End Sub➤ If you try typing =DoubleValueSub(100) in a cell, Excel will return an #NAME? Error.
Subs cannot be called directly from worksheet cells; they only work from the VBA editor or through buttons/macros.
➧ Subs perform actions and cannot return a value to a cell.
➧ This distinction is important when deciding whether you need a procedure for calculations (Function) or for actions (Sub).
➧ Subs handle errors by performing actions such as showing message boxes or logging errors.
➧ Functions handle errors by returning a value to the worksheet, which can be text, numbers, or a custom indicator.
➧ Choosing between a Sub or Function affects how errors are communicated to the user.
Frequently Asked Questions (FAQs)
Can a Function perform actions like a Sub?
Yes, a Function can perform actions such as formatting cells or manipulating data. However, its primary purpose is to return a value. Using a Function purely for actions without returning anything is possible, but not recommended.
Can a Sub return a value?
No, a Sub cannot return a value directly. It is designed to perform tasks or actions in the workbook. If you need to produce a value, you should use a Function instead.
Can Functions be called from a Sub or worksheet cell?
Yes, Functions can be called from a Sub, another Function, or even directly from a worksheet cell as a formula. Subs can only be called from other Subs, buttons, or VBA events.
Are there performance differences between Functions and Subs?
Performance differences are minimal in most practical scenarios. The choice depends on whether you need to return a value (Function) or just perform an action (Sub).
How does error handling differ?
Subs can handle errors using message boxes or logs. Functions must handle errors and still return a value, which sometimes requires converting an error into a specific return value.
Wrapping Up
Understanding the differences between Functions and Subs in VBA is crucial for writing clean, efficient, and maintainable Excel macros. Functions are ideal when you need to calculate and return values, while Subs are better suited for performing actions or procedures in your workbook.
By applying the scenarios demonstrated in this article, you can choose the right approach depending on your task, reduce errors, and improve the clarity of your VBA code. Recognizing how each responds to inputs, outputs, and errors will make your Excel automation more predictable and easier to manage.
