Excel VBA Function Tutorial: Return, Call, Examples

What is a Function?

A function is a piece of code that performs a specific task and returns a result. Functions are mostly used to carry out repetitive tasks such as formatting data for output, performing calculations, etc.

Suppose you are developing a program that calculates interest on a loan. You can create a function that accepts the loan amount and the payback period. The function can then use the loan amount and payback period to calculate the interest and return the value.

Why use functions

The advantages of using functions are the same as the ones in the above section on why use subroutines.

Rules of naming functions

The rules for naming functions as the same as the ones in the above section on rules for naming subroutines.

VBA Syntax for declaring Function

Private Function myFunction (ByVal arg1 As Integer, ByVal arg2 As Integer)
    myFunction = arg1 + arg2
End Function

HERE in the syntax,

Function demonstrated with Example:

Functions are very similar to the subroutine. The major difference between a subroutine and a function is that the function returns a value when it is called. While a subroutine does not return a value, when it is called. Let's say you want to add two numbers. You can create a function that accepts two numbers and returns the sum of the numbers.

1. Create the user interface
2. Add the function
3. Write code for the command button
4. Test the code

Step 1) User interface

Add a command button to the worksheet as shown below

Set the following properties of CommanButton1 to the following.

Your interface should now appear as follows

Step 2) Function code.

1. Press Alt + F11 to open the code window
2. Add the following code

Private Function addNumbers(ByVal firstNumber As Integer, ByVal secondNumber As Integer)
    addNumbers = firstNumber + secondNumber
End Function

HERE in the code,

Step 3) Write Code that calls the function

1. Right click on btnAddNumbers_Click command button
2. Select View Code
3. Add the following code

Private Sub btnAddNumbersFunction_Click()
    MsgBox addNumbers(2, 3)
End Sub

HERE in the code,

Step 4) Run the program, you will get the following results

Download Excel containing above code

Download the above Excel Code

Summary:

• A function is a piece of code that performs a specific task. A function returns a value after execution.
• Both subroutines and functions offer code reusability
• Both subroutines and functions help break down large chunks of code into small manageable code.