How to Create Custom Excel Functions? (with Examples)
To create these excel custom function we need to write a code to create our own functions and that is called “User-Defined Functions”. Custom Functions are user-defined functions in excel, so create these functions you need to be a top VBA coder.
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). 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.
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.
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.
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.
Like this, by using VBA coding we can custom functions of our own.
Things to Remember
- Create Custom functions are nothing but User Defined Functions.
- To create custom 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 –
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
