VBA Sub Vs Function in Excel (5 Differences)

Get FREE Advanced Excel Exercises with Solutions!

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.

Function to Show the Differences between Sub Vs Function in Excel VBA

Now, if you go to any worksheet in our workbook, you’ll get New_Function in the list of Excel functions.

Calling Function to Show the Differences between Sub Vs Function in Excel VBA

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.

Running a Macro to Show Sub vs Function in VBA in Excel

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

Function to Show the Differences between Sub Vs Function in Excel VBA

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."

Sub Code to Show the Differences between Sub Vs Function in Excel VBA

Now press the Run button in the VBA toolbar.

Running Sub to Show the Differences between Sub Vs Function in Excel VBA

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

Calling Sub to Show the Differences between Sub Vs Function in Excel VBA

Now if you run this Sub, it’ll call the New_Sub and the same message box will appear, as in section 3.

Output to Show the

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)

Formula to Show the Differences between Sub Vs Function in Excel VBA

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.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , and premium Excel consultancy services for Excel and business users. Feel free to contact us with your Excel projects.
Rifat Hassan
Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo