Function and Sub in Excel VBA
A function is a small set of instructions that performs a specific task and always returns a value. But in the case of the sub, it does not return a value. Functions and subroutines are very helpful when your program becomes large. In this tutorial, you will learn about the use of function and Sub in Excel VBA.
Function in VBA
When you want Excel VBA to do something and give you a result, a function should be used. Click Insert > Module in the Visual Basic Editor to add a function to your project. For example, the function named Area.
Function Average (a As Double, b As Double) As Double Average = (x * y)/2 End Function
Explanation: This function receives two decimal numbers (Double) and returns one decimal number (Double) as output. Use the function’s name in your code to return the final result, like returning ((x * y)/2) from the ‘Average’ function.
You are now able to call this function from a different section of your code by entering the function name followed by the required values.
Add a command button on the worksheet and write the following code inside its click event:
Dim z As Double z = Average (25, 5) + 2 MsgBox z
Explanation: Since the function returns a value, you must capture it by assigning it to a variable. Here we use Z as a variable to save the value. Next, you can insert an additional value into the variable, if you wish. Finally, use a message box to display the value.
The result shown after clicking the command button is the outcome of the assigned macro:
Sub in VBA
When you need Excel VBA to do something, you usually write a Sub procedure. Click Insert, then Module in the Visual Basic Editor to place a Sub procedure inside. For example, a sub named Area.
Sub Area(x As Double, y As Double) MsgBox x * y End Sub
Explanation: This sub uses two parameters, and both are of the Double data type. It does not have a return type! To run this subroutine from somewhere else in your code, just type its name and include a value for each argument it needs.
Put a command button on the worksheet, then insert this code line into its click event:
Area 3, 5The result of clicking the command button:
Do you understand how a Function is different from a sub-procedure? The function returned the value 15. The final result was shown after adding 2 to the previous value. When we called the subroutine, we no longer had control over the result (15) because a subroutine does not return a value.
FAQ
What is the difference between a function and a Sub in Excel?
The main difference between a Function and a Sub is that a Function can return a value, but a Sub can not.
What is a VBA sub?
The VBA sub is an essential component of Excel VBA programming. It works similar way Excel function works, but the main difference is that a function can return a value, but a sub can not.
How to Use a Sub in Excel VBA?
When you need to perform some action in Excel VBA, you can use a sub. In the Visual Basic editor, click on Insert, then New Module, and place your subroutines there.
1/5 Completed! Explore functions and subs ➝
Next Chapter: Application Object