How to create User Defined Function in Microsoft Excel

Intro to User-Defined Function in Microsoft Excel

It is well known that Microsoft Excel has reached a source of built-in functions that make our life very easy when analyzing data. However, it is not the end, and this is because most of the times we may come up with a situation where all the formulae provided need to be more suitable or sufficient for the task we are doing.

In such cases, we can efficiently create our function in Microsoft Excel according to our needs with the help of VBA (Virtual Basic for Application) and such functions are known to be the User Defined Functions (UDFs).

Moreover, a User Defined Function (UDF) can be primarily used within the worksheet in the same way as we are making use of the normal excel functions that include the following:

1. SUM.
2. AVERAGE, etc.

And these functions can be created as per accordance to the user's requirement by making use of the Virtual Basic for Application (VBA) and will be helpful in providing an ease to the user's tasks in Microsoft Excel.

Besides all these, any User Defined Function (UDF) has a basic rule; which is necessary to be defined by the user under the FUNCTION procedure instead of a subroutine procedure, and with the help of the FUNCTION procedure we can effectively create UDF's and can make use of them inside the Microsoft Excel as the general excel functionlike:

• VLOOKUP.
• SUM.
• AVERAGE, etc.

How to create a custom or a User Defined Function in Microsoft Excel?

First of all, an individual needs to open the Visual Basic Editor (VBE), and it should be kept in mind that the VBE begins on the new window and does not close our Excel spreadsheet.

And the easiest, as well as the simplest way that can be used to open (Virtual Basic Editor) is none other than by making use of the shortcut button from the keyboard:

After the opening of the VBE, we need to add a new module in which we will be writing our functions.

We will right-click on the VBA project pane to selectInsert -> Module. As soon as we click on the Module, an empty module window appears on the screen where we need to specify our custom function respectively.

Rules which need to be followed by an individual while creating a User Defined Function:

1. A user-defined function must always start with the "Function" and generally ends with the "End Function" effectively.
2. And the "Function" must be followed by the function's name. This is a title an individual creates and employs to our function so that an individual could easily identify and use it later on.
3. And it should be remembered that an individual should not name the UDF's name as similar to the standard Excel functions, and If we do this, then the standard function will always be executed.
4. And the name of the respective User Defined Function cannot match the address of the particular cell used in the given worksheet.
5. The particular function's arguments which are primarily listed in parentheses, as this is the data with which it will work, and we can have more than one argument. If in case there are more arguments, it should be separated by a comma.
6. And if in case the function inside the UDF does not make use of the arguments, then in that case, an individual needs to create a function withno arguments.
7. And an individual also needs to determine the variable which is to be used by the user defined function.
8. After that, an individual will put several VBA statements that can perform calculations by the help of the arguments passed to the function effectively.
9. Ultimately, an individual needs to write a statement that will assign the final value to a given variable with the same name as the functions.

Examples

Example #1 - User Defined Function that can be used to find the Area of the Triangle in Excel

It was the fact that the students from the mathematical stream always come up with a mathematical problem in which they need to find out the area of a triangle. Though it was well known that it has a very easy formula to work around it, the students would be pleased if they had some function that could compute the area for a triangle.

So we will be following the steps mentioned below to create a function which can compute the area of a triangle effectively:

Step 1:In the very first step we need to insert a new module which is presents just under Visual Basic Editor (VBE). And we will Open VBE > by clicking on Insert tab >and then select Module from it.

Step 2: In this step we will be defining a new FUNCTION procedure by assigning it the name as TriangleArea.

Since it is a FUNCTION procedure, and we are required to define arguments for the purpose of calculate the area of the triangle while defining the function as well.

Step 3:After that we will assign two arguments that are height and the base in TriangleArea function which are in the given bracket. And these are considered to be the mandatory arguments that mean that an individual needs to provide the values for the height as well as for the base in order to calculate the area of a triangle.

Step 4:And now, in this step we will be writing a code that are responsible for returning the area for a triangle when we will be providing the height and the base values within this procedure.

Step 5:Now, in this step, in the given cell C2 we will start typing the given formula=TriangleArea (A2, B2) in order to compute out the area of a triangle and then we will provide the height and the base as an argument to the same given formula.

Step 6:After that, we will close the given parentheses to complete the formula and we will press the Enter key from the keyboard to encounter the area of a triangle with a height of 12 and a base of 7.5.

And after that, we will drag out the particular formula to get the area for the second and the third values of height and base effectively.

# Example 2: User Defined Function to find the Square of any Number

Let us assume that we want the squared value of any particular number, either in the form of an integer data type or float type. And no such function will give us the squared value for any given number. So, let us create one that can efficiently do the task for us in Microsoft Excel.

Step 1:In the first step and in the same Module, we will start defining a new FUNCTION procedure to square a number with SquareNum.

Step 2:And then, we are required to add an argument for the given function as well as making use of the num as an argument to the particular function available within parentheses. And as soon as we provide an argument within the given function, we will be able to see the End Function statement.

Step 3:In this step, within the given function, we can add the piece of code or the lines to obtain the Square of the particular numbers. As num is the argument to the SquareNum function, we have two options for getting the desired output:

One of the methods or ways is to multiply num by itself, or the other way is to make use of the power operator "^" over the num .And in this example, we will be going with the other way as it makes the code more versatile and less complex.

Step 4: After that, we will save the code by just clicking on the "Save button", which is present under the Virtual Basic for Editor (VBE), so that we can use the function named SquareNum under the Microsoft Excel sheet as well.

And let us assume that we are having a different set of numbers on which we need to find the square values for those. So, in that case, we can make use of the SquareNum function that is defined just above to get the outputs effectively.

Step 5:And in cell B2, we will start typing the Square and can encounter the function that we have just defined for the purpose of squaring a number after that Double-click on it to select it.

Step 6:After that, we will be providing A2 as an argument to this function so that it will square the number that are present in cell A2.

Step 7:After performing the above steps, we will be closing the parentheses in order to complete the formula and press Enter key from the keyboard to get encountered with the Square of number 11.

Step 8:In this step, we will be now dragging and pasting out the formula across the remaining cells to get the square roots of the left over numbers.

What are the types of the User Defined Function available in Microsoft Excel?

The various types of the User Defined Function which are available in Microsoft Excel are as follows:

1. No arguments.
2. With one argument.
3. Making use of the Array as an argument.
4. With the help of multiple arguments.

NO ARGUMENTS

It was well known that Microsoft Excel has various standard functions that do not require arguments (NOW, RAND, TODAY).

• For example, the TODAY function will returns the current date, whereas the RAND function primarily returns a random number in between 0 and 1, and we do not need to specify any values to them effectively.

And the most important point is that we can also create such a function in Virtual Basic for Application (VBA). Below mentioned code will write the name of our worksheet into a cell respectively:

Code:

And we should notice that there are no arguments in the parentheses just after the function name it is because the outcome which needs to be returned does not depend upon any values in our working file, and it does not make requirement of any arguments.

Besides all these, the mentioned above the code would be defining the output of the function as a string data .And if in case we do not specify the data type, then it would be automatically determined by Excel.

With one argument

Now we will be moving further by creating a simple function that will effectively work with one single argument in one particular cell.

And our main task is to extract the last word from the given text string, so for that we will be making use of the below-mentioned code:

Code:

The_Textis the value from the selected cell and we can indicate that this would be a text value.

The StrReverse function returns text in the reverse character order, whereas the particularInStrfunction is used to determine the first space's position. And by the help of the Left function, we can get all characters which are specifically ending with the first space we can also remove all the spaces by just making use of the Trim. StrReverse helps in changing the characters' order; after all we will get the last word from the given text.

Since this function usually takes a cell value and we don't need to make use of the Application. Volatileas in this case.

Making use of the Array as an Argument

We all knew that most of the Microsoft Excel functions basically make use of the arrays of values as an argument.

And the respective code below will help in the creation of a function that will sums all even numbers in a specified range of cells.

Code:

In theabove code, the NumRangeargument is primarily considered as therangeRange, which means that the given function will make use of the original data array. It looks like as below:

Moreover, theVarianttype provides a "non-dimensional" container to store the data. As such variable can used to store any kind of the data types which are allowed in VBA, including numeric values, text, dates etc.

And the code has aFor Eachargument as it takes each cell and checks to see if it contains a number, and If in case it does not contain the number, then nothing happens and it moves on to the next cell respectively. If a number is found, then it will check whether it is even or not by the help of the MOD function.

All the even numbers are summed up in theResultvariable, and when the loop is finished, theResultvalue will be effectively assigned to theSumEvenvariable and then passed to the function.

Multiple arguments In Excel

Many of the Microsoft Excel functions have multiple arguments. That's why it is much more important to be able to create UDFs with multiple arguments effectively.

Code:

The code as mentioned above primarily has 3 arguments: a range of values, a lower bound for a numeric field, as well as an upper bound for a range.

The first one isthe rngCells As range. It is the rangeRange of cells that helps in searching for the maximum value in it. And in contrast, the second as well as the third arguments (MinNum, MaxNum) are usually specified without a type declaration meaning that the Variant data type will be applied to them automatically.

Furthermore, the Virtual Basic for Application (VBA) uses 6 different numeric data types. And specifying only one of them means limiting the use of the function. Therefore, it is better if Microsoft Excel determines the numeric data type.

TheFor Each loop uses all the values in the selected range sequentially. The numbers in the rangeRange from the maximum to the minimum value are written to a special array -arrNums, with the help of the standardMAX FUNCTION to find the largest number in this Array effectively.

What are the points that an individual has to remember in Microsoft Excel?

The important things which need to be remembered by an individual while working with the User Defined Function in Excel are as follows:

1. An individual should remember that a User Defined Function (UDF) through VBA helps to create custom formulas that will reduce the time required to perform any task and simplify the tasks.
2. The User Defined Function (UDF) is much limited to the sheet only for which it has been created. However, an individual can copy the code as well as paste it to other sheets where he/she wants to make use of it.