Excel Custom Functions

How to Create Custom Excel Functions? (with Examples)

To create a custom function, we need to write a code for the working of our own functions, and that is called “UDFUDFUser Defined Function in VBA is a group of customized commands created to give out a certain result. It is a flexibility given to a user to design functions similar to those already provided in Excel.read more.” Custom Functions are user-defined functions in excel, so to create these functions, you need to know VBA coding thoroughly.

Custom-Excel-Functions

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Excel Custom Functions (wallstreetmojo.com)

Example #1 – Add Any Two Numbers

For example, if you want to add any two numbers, then we will show you a simple User Defined Function (UDF).

  • Press Alt+F11 and insert module.
Custom Excel Function Example 1
  • Write code in a module to create a custom function.

Any custom function should start with the word “Function,” followed by the formula name.

Custom Excel Function Example 1-1

Any function has its arguments or parameters similarly to custom functions, too; we need to define those parameters. Since we are adding just two numbers, let us define those parameters and assign data types to those argument variables.

Custom Excel Function Example 1-2

Once the parameters are defined with a data type, we need to define the result outcome data types as well. Let’s define the outcome as the “Integer” value.

Custom Excel Function Example 1-3

Inside this “Function Procedure,” we will write the formula code.

Code:

Function Addition(Num1 As Integer, Num2 As Integer) As Integer

  Additiona = Num1 + Num2

End Function

This says the function name “Addition” result will be the sum of Num1 and Num2 values.

  • Now come back to the worksheet and enter any two integer numbers.
Custom Excel Function Example 1-4
  • We will add these two numbers now. Open equal sign and enter the custom function name “Addition.”
Custom Excel Function Example 1-5

Select the first and second numbers by entering separator as comma (,).

Custom Excel Function Example 1-6
  • Hit the enter key to get the result.
Custom Excel Function Example 1-7

Wow!!! Like the SUM function, we got the result of the sum of two numbers.

Now, look at these numbers.

Custom Excel Function Example 1-8

Try adding these two numbers now.

Custom Excel Function Example 1-9

We got the error value because the arguments “Num1 & Num2” data type is “Integer,” i.e., these two arguments can hold values between -32767 to 32767, so anything more than this will cause these errors.

Now try adding these two numbers.

Custom Excel Function Example 1-10

Even this will cause below error value.

Custom Excel Function Example 1-11

Even though individual argument values are within the limit of the Integer data type, we still got this error because the total sum of these numbers is more than the integer limit value.

Since we have declared the result type also as “integer,” the result of the addition of two numbers should also be an integer number limit.

Example #2 – Add All Odd Numbers

Excel doesn’t have any built-in function, which can add all the odd numbers from the list of numbers. But nothing to worry, we will create a custom excel function to support this.

Add Odd Numbers Example 2

Open the VBA editorVBA EditorThe Visual Basic for Applications Editor is a scripting interface. These scripts are primarily responsible for the creation and execution of macros in Microsoft software.read more window and give a name to the Function procedure.

Add Odd Numbers Example 2-1

Give the parameter for this function as “Range.”

Add Odd Numbers Example 2-2

This means that for this function, we are providing the input value for this function as the “Range” of cells values.

Since we need to loop through more than one cell, we need to use the “For Each” loop in VBA, so open the “For Each” loop.

Add Odd Numbers Example 2-3

Inside this loop, add the below code.

Code:

Function AddOdd(Rng As Range)

 For Each cell In Rng
  If cell.Value Mod 2 <> 0 Then AddOdd = AddOdd + cell.Value
 Next cell

End Function

We have to use the “MOD” function to test the number. When each cell value is divided by the number 2 and the remainder value is not equal to zero, then our code should add all the odd number values.

Now come back to the worksheet and open the excel custom function.

Add Odd Numbers Example 2-4

Select the number range from A1 to D8.

Add Odd Numbers Example 2-5

Hit enter key to get the “odd” number result.”

Add Odd Numbers Example 2-6

So, in the range A1 to D8, we have the sum of odd numbers is 84.

Example #3 – Add All Even Numbers

Similarly, the below function will add all the even numbers.

Code:

Function AddEven(Rng As Range)

 For Each cell In Rng
  If cell.Value Mod 2 = 0 Then AddEven = AddEven + cell.Value
 Next cell

End Function

This function will add only an even number. In this case, we have used the logic of if each cell value is divided by 2, and the remainder is equal to zero, then code will add only those cell values.

 Add Even Numbers Example 3

Like this, by using VBA coding, we can create custom functions of our own.

You can download this Custom Functions Excel Template here – Custom Functions Excel Template

Things to Remember

This has been a guide to Excel Custom Functions. Here we learn how to create our own custom function using code and a downloadable excel template. You may learn more about excel from the following articles –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>