VBA Functions

A function is a piece of code that performs a specific task and returns the result. Functions are mainly used to perform repetitive tasks such as performing calculation, formatting data for output, etc.

In VBA, you can create a function or a user-defined function that can be used in a worksheet just like regular functions.

This is very helpful when the existing excel function is not enough. In these cases, you can create your custom user-defined function to serve your specific needs.

You can place a function into a module. Let’s see step by step:

Step 1: Go to the Developer checkbox.

Step 2: Click on the Visual Basic editor.

Step 3: Then click on the Insert button.

Step 4: Select the Module option.

Syntax

For example, Suppose a function named Sum in the following code, such as:

Explanation: In this example function has two arguments (of type double) and a return type (also of type double). The function name is Sum to indicate which result you want to return such as (x + y).

Now you can use call this function from somewhere else in your code by only using the name of the function (Sum) and giving a value for each argument.

Insert a command button on your worksheet and add the following code.

Explanation: You can use another variable z to store the result. The function returns a value, so you have to catch these values in your code. Also, you can add another value to this variable. Display the value using a MsgBox.

After clicking on the command button on the sheet, you will get the following result.

Example

A function is very similar to the subroutine. The only difference between a function and subroutine is that a function returns a value when it is called, but a subroutine does not return a value when it is called.

Suppose you want to find the area so you need to create a function that accepts the values can be returned with the function name itself.

Step 1: Add a Command Button to the worksheet.

1. Then set the Name property of the Command button.

2. Set the Caption property of the Command button.

3. Now, the interface looks like the below screenshot.

Step 2: Open the code window and write the following code.

Step 3: Write the code that calls the function.

1. Right-click on the btnArea_Click command button.
2. Select the View Code option.
3. And add the following code.

Step 4: Run the above code, and you will get the following output as shown in the below screenshot.