Function and Sub in Excel VBA – Complete Tutorial

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.

Function

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

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, the 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, 5

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

1/5 Completed! Explore functions and subs ➝
Next Chapter: Application Object