WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Excel Custom Functions

Excel Custom Functions

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

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 “UDF.” Custom Functions are user-defined functions in excel, so to create these functions, you need to know VBA coding thoroughly.

Custom Excel Functions

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 (,).

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

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 editor 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

  • Create Custom functions are nothing but User Defined Functions.
  • To create custom functions, one should have 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 our own custom function using code and a downloadable excel template. You may learn more about excel from the following articles –

  • List of VBA Functions
  • Custom List in Excel
  • Custom Number Format in Excel
  • Excel Functions
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Custom Functions Excel Template

Special Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More