Excel Custom Functions

To create a custom function, we need to write a code for our functions' working, called “UDF.” The custom functions are user-defined in Excel, so you need to know VBA coding thoroughly to create these functions.

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

• First, press Alt+F11 and insert the module.

• Write code in a module to create a custom function.

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

Any function has its arguments or parameters, similar to custom functions. We need to define those parameters. Since we add just two numbers, let us define those parameters and assign data types to those argument variables.

Once the parameters are defined with a data type, we also need to define the outcome data types. First, let us define the outcome as the "Integer" value.

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

The function name "Addition" result will be the sum of "Num1" and "Num2" values.

• Now return to the worksheet and insert any two integer numbers.

• We will add these two numbers now. Open the equal sign and enter the custom function name "Addition."

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

• Press the "Enter" key to get the result.

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

Now, look at these numbers.

We will try adding these two numbers now.

We got the error value because the arguments "Num1" and "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, we will try adding these two numbers.

Even this will cause the below error value.

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

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

Excel does not have any built-in function which can add all the odd numbers from the list of numbers. But nothing to worry about. We will create a custom Excel function to support this.

We must open the VBA editor window and give a name to the function procedure.

Give the parameter for this function as "Range."

We provide the input value for this function as the "Range" of cell 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.

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, our code should add all the odd number values.

We need to return to the worksheet and open the Excel custom function.

Select the number range from A1 to D8.

Press the "Enter" key to get the "odd" number result."

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

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 that if each cell value is divided by 2, and the remainder equals zero, the code will add only those values.

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

• Creating custom functions is nothing but "User-Defined Functions."
• To create custom functions, one should know advanced VBA coding skills.
• While making the custom Excel functions, watching arguments and their data types is important.