VBA Sub Vs Function in Excel (5 Differences)

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.


Download Practice Workbook

Download this practice book to exercise the task while you are reading this article.


5 Differences between Sub Vs Function in Excel VBA

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 of the workbook.

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


2. VBA Sub Vs Function: 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.


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


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 requirement. Do you have any questions? Feel free to ask us.

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

ExcelDemy
Logo