In this article, I’ll compare Sub Vs Function in VBA in Excel. Sub and Function are two important and widely used features that we use while working with VBA in Excel. In this article, I’ll focus on the main differences between these two features.
Here are the 5 vital differences between Sub and Function in VBA in Excel.
1. VBA Sub Vs Function: A Function Creates a User-Defined Function, Whereas a Sub Creates a Macro in Excel
A function creates a user-defined function in the workbook in Excel. On the other hand, a sub creates a Macro in Excel.
Here we’ve created a Function called New_Function.
Now, if you go to any worksheet in our workbook, you’ll get New_Function in the list of Excel functions.
On the other hand, if you create a Sub, you’ll get it in the list of Macros.
Here we’ve created a Sub called New_Sub.
It’ll be available in the list of Macros in the workbook.
Read More: How to Use VBA User Defined Function
2. VBA Sub Vs Function in Excel: A Function Can Return a Value, but a Sub Can’t
A function returns a value when it is called on a worksheet.
For example, let’s go back to our function New_Function.
We inserted a line:
New_Function=10
Now, if we call the Function in any worksheet in the workbook, it’ll return 10.
But a Sub can’t return a value like this.
Related Articles: How to Return a Value in VBA Function
3. VBA Sub Vs Function: A Sub Can be Run Directly in a Module, but a Function Can’t
You can run a sub directly in a module. Just press the Run button above the module, in the VBA toolbar.
Here we’ve our Sub called New_Sub. We’ve added this new line inside it:
MsgBox "The Macro is Run."
Now press the Run button in the VBA toolbar.
The code will run a message box will appear declaring “The Macro is Run.”
But you can’t run a Function in this way.
4. VBA Sub Vs Function: A Sub Can be Called within Another Module in Excel, but a Function Can’t
You can call a Sub within another Module in VBA.
Here we’ve created a new Sub called Call_Sub. Within it, we’ve called the Sub New_Sub by the line:
Call New_Sub
Now if you run this Sub, it’ll call the New_Sub and the same message box will appear, as in section 3.
But you can’t call a Function in this way within another Module.
5. VBA Sub Vs Function in Excel: A Function Can be Used in a Worksheet as a Function, but a Sub Can’t
This is almost the same as the difference in Section 2.
You can use a Function directly in your worksheet.
Here we’ve created a Function called Add_Ten.
It takes an integer as the input, adds 10 with it, then returns the output.
You can use this function directly in your worksheet.
Here I’ve entered 15 in cell B3 of my worksheet.
Then in cell D3, I’ve entered the formula:
=Add_Ten(B3)
Click Enter. And it’ll return 25 in cell D3.
But you can’t use a Sub directly in the worksheet like this.
To use a Sub, you’ve to go to the section Macro, then select the Sub, and run it.
Download Practice Workbook
Download this practice book to exercise the task while you are reading this article.
Conclusion
So these are the main differences between a Sub and a Function in VBA in Excel. Both have their own advantages and disadvantages. To use them correctly, first, you have to identify your demand properly, then select the one that suits best your requirements. Do you have any questions? Feel free to ask us.
