Excel Custom Functions

Custom Functions in Excel

We all know excel comes with plenty of built-in functions to support our work with excel. If you are not satisfied with the results of the built-in function you can create your own functions i.e. custom functions. To create these excel custom function we need to write a code to create our own functions and that is called “User-Defined Functions”.

In this article, we will show you how to create custom functions in excel.

How to Create Custom Excel Functions?

Custom Functions are user-defined functions in excel, so create these functions you need to be a top VBA coder.

In this article, we will make an attempt to guide you about creating custom excel functions.

Custom Excel Function #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). Open Visual Basic Editor Window.

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

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

Once the parameters are defined with data type we need to define the result outcome data types as well. Let’s 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

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.

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

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

Hit enter key to get the result.

Wow!!! Like SUM function we got the result of the sum of two numbers. Now, look at these numbers.

Try adding these two numbers now.

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.

Even this will cause below error value.

Even though individual argument values are within the limit of Integer data type still we 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.

Custom Excel Function #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.

Open the VBA editor window and give a name to the Function procedure.

Give the parameter for this function as “Range”.

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

Since we need to loop through more than one cell we need to use the “For Each” loop, 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 then our code should add all the odd number values.

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

Select the number range from A1 to D8.

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

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

Custom Excel Function #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.

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

Things to Remember

• Create excel Custom functions are nothing but User Defined Functions.
• To create custom excel functions one should have the knowledge of advanced VBA coding skills.
• While creating the custom excel functions it is important to have an eye on arguments and their data types.

Recommended Articles

This has been a guide to Excel Custom Functions. Here we learn how to create custom functions in excel using Addition, AddOdd & AddEven formulas and downloadable excel template. You may learn more about excel from the following articles –

• Excel Show Negative Numbers
• What is a Grouping of  Rows?
• Inserting Columns in Excel
• List of VBA Functions
• Create Custom List in Excel
• RANKX in Power BI
• FIND Function in VBA