Excel User Defined Function (Table of Contents)
Introduction to Excel User Defined Function
Excel has reached a source of inbuilt functions to make your life easy when it comes to analyzing the data. However, that is not the end, right? Sometimes, you might come up with a situation where all the formulae provided are not suitable for the task you are doing. In such cases, you can create your own function in Excel with the help of VBA such functions are called User Defined Functions (UDF’s). UDF’s can be used within the worksheet the same way you are using the normal excel functions such as SUM, AVERAGE, etc. These functions can be created as per the user requirement through VBA and can ease the tasks the user has to do in Excel. In this article, we will discuss User Defined Function in Excel.
Any UDF has a basic rule, you need to define it under FUNCTION procedure instead of a subroutine procedure (sub-procedure we generally use). Using the FUNCTION procedure, we can create UDF’s and can use them inside Excel as the general Excel functions such as SUM, AVERAGE, VLOOKUP, etc.
In this article, we will learn how to create UDF’s as per the user requirement and use them in Excel to ease the tasks.
Examples to Create User Defined Function
Let’s understand how to create the User Defined Functions in Excel with some examples.
Example #1 – User Defined Function to Find the Area of Triangle
Students from the mathematical stream may always come up with a problem where they need to find out the area of a triangle. Though it has a very easy formula to work around with, students will be pleased if they have some function that can compute the area for triangle. Follow the steps below to create a function that can compute the area of a triangle.
Step 1: Insert a new module under Visual Basic Editor (VBE). Open VBE > click on Insert tab > select Module.
Step 2: Define a new FUNCTION procedure with the name as TriangleArea. This name will be used to find this function in Excel.
Since it is a FUNCTION procedure, we need to set the arguments that are needed to calculate the triangle area while defining the function.
Step 3: After TriangleArea, mention height and base as two arguments to this function in bracket. These are the mandatory arguments. Meaning, the user needs to provide values for height and base in order to calculate the area for triangle. The FUNCTION procedure is now complete (you can see End Function clause) and we can write pieces/lines of code within this procedure.
Step 4: Now, within this procedure, we will write a code that will return the area for a triangle when we provide the height and base values. Since the area of the triangle is formulated as, we will formulate it under the code as well as assign it to TriangleArea.
This is it, the function is created and can be used. Save the code and we will see how it works for the different values of height and base. Suppose we have different height and base values and we need to capture the area of a triangle for those values using the function we just have created.
Step 5: In cell C2, start typing the formula =TriangleArea(A2,B2) in order to compute the area of a triangle and provide height and base as an argument to the same formula.
Step 6: Close the parentheses to complete the formula and press Enter key to see the area of a triangle with height 12 and base 7.5.
Drag the formula to get the area for second and third values of height and base.
Example #2 – User Defined Function to Square any Number
Suppose we want the squared value of any number (integer or float). There is no such function that gives you the squared value for any number. Let’s create one that can do the task for you in Excel.
Step 1: In the same module, start defining a new FUNCTION procedure for squaring a number with name SquareNum.
Step 2: We need to add an argument for the function. Use num as an argument to the function within parentheses. As soon as you provide argument within the function, you will be able to see the End Function statement which means the arguments we have provided are accepted,
Step 3: Within the function, we can now add the piece of code/lines to get the square of numbers. As num is the argument to the SquareNum function, we have two options to get the desired output. One way is to multiply num by itself (num * num ex. 2 * 2) or another way is to use the power operator “^” over num function (num^2 ex. 3^2). I will go with the other way as it makes the code more versatile.
Step 4: Save the code by click on the Save button under VBE so that we can use the function named SquareNum under Excel sheet.
Suppose we have a set of different numbers as shown in the image below and we need to find the square values for those. We can use the SquareNum function defined just above to get the result.
Step 5: In cell B2, start typing the Square and you can see the function we just have defined for squaring a number. Double click on it to select the function.
Step 6: Provide A2 as an argument to this function so that it will square the number present in cell A2.
Step 7: Close the parentheses to complete the formula and press Enter key to see the square of number 11 (number in cell A2).
Step 8: Drag and Paste the formula across the remaining cells to find out the square roots of the numbers provided.
This article emphasized on creating User Defined Functions in Excel which can be used to reduce the manwork. Let’s wrap the things up with some points to be remembered.
Things to Remember About User Defined Function in Excel
- UDF’s through VBA helps to create the custom formulas which can reduce the time we need to do any task and simplifies the tasks as well.
- The UDF is limited to the sheet under which it is created. However, you can copy the code and paste it to other worksheets where you want to use them.
Recommended Articles
This is a guide to User Defined Function in Excel. Here we discuss how to create User Defined Function in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
