© Roy Cox (2005 - 2020)

.

What is a User Defined Function?

User Defined Functions or UDFs are Excel functions whose properties are custom defined by the user. They can be tailored to specific user needs and greatly improve productivity as they can help you do things for which there are no built-in Excel functions. In other words, UDFs allow you to create your own functions, with your own pre-defined parameters and options. Let’s take a closer look at how this works and how you can create your own custom formulas.

How to Create User Defined Functions

You create User Defined Functions much like Macros. First you need to open the VB Editor window and add a module. Press the Alt + F11 key combination when your workbook is open. Alternatively, you can click the Visual Basic icon in the Code Group of the Developer Tab of the Ribbon In the VBA window, go to Insert -> Module. Now you are ready to start writing your first UDF. Here’s a simple one to calculate the area of a rectangle or square. We are going to need a procedure to create the User Defined Functions. Procedures are defined as code blocks that can run multiple times. There are 2 types of procedures: 1 . Subroutines or Sub 2 . Functions The main difference between the two is how they return a value. A subroutines only runs the code, but a function can return a value as well. It is a Function that we need to create the UDF. A function should start with a Function statement and have a name, and end with an End Function statement. All codes should go into these statements. Subroutines, on the other hand, use Sub and End Sub statements. For obvious reasons let’s call our Function Area. Remember there are certain rules to follow similar to creating a macro or subprocedure. The name of the function cannot have spaces in it, you can replace a space with an underscore. You cannot name a function with the name of a cell reference. For example, you can not name the function A1 as it also refers to a cell in Excel worksheet. Your function should not the same name as that of an existing Excel Function. If you do this, Excel would give preference to the in-built function.

Argument(s)

A Function can work without any arguments, similar to the NOW, or TODAY() Functions. To use a function with argument(s), they should be defined in the code as well. Arguments are defined next to the function’s name, between brackets. Argument names can be used as constants in the code. The arguments of your functions can be made optional. A common example is the VLOOKUP function which can take 3 or 4 arguments. The last argument, range_lookup, is an optional argument that you can choose to omit, if you intend to use it’s default value. If you omit the last argument of the VLOOKUP function then it takes this as TRUE (or 1) and perform the calculations accordingly. The same logic can be used with a UDF as well. To make an argument optional, you should use the Optional statement before the argument name and define its default value. As you can see the sample Function takes two Arguments to return a Double. The second is set as Optional which means that you do not need to use it. In this case if it is missing the Function assigns it the same value as the Length, i.e. do this when the shape is a square. It’s also a good practice to specify what kind of argument the function expects. In this example, because we will be using lengths which may be ecimalised, we can use Double type. If you don’t specify a data type, VBA would consider it to be a variant (which means you can use any data type). Note that the Function is specified as a Double data type as well. This will tell Excel that the result of the formula will be a Double data type. You can now use your Function as you would any other Function by adding it to a cell =Area(10.5,6) will return the area of a rectangle of those dimensions, i.e 56.

Save and distribute

Now that you have written and tested your function, the next step is saving it for future use or even send them to another user. First of all, a UDF is VBA code. You have a few options: You can save the file as an XLSM file which means an Excel file with macros. This makes the UDF specific to that file. You cannot use your custom function in other files. if you want to use it with all workbooks or pass onto a colleague or friend then you can save your custom functions in an add-in file, XLAM. You can think of add in files as files containing VBA code that run in the background. You can set an add-in file to open automatically with Excel. So, you can use your functions every time. read my addins page for more details. Similarly, you can add the Function to your PERSONAL.xlsb file which is a personal macro workbook that you can save your macros in and use with every workbook that is open. Because UDFs are macros, you can save your functions into your Personal.xlsb as well. If you use an addin or PERSONAL.xlsb to store your addin then you should use Personal.xlsb or the addin’s namewhen calling your function, e.g if you save the Area function into your Personal.xlsb, you need to call the function like this: =Personal.xlsb!Area(10.5,6) To avoid having to type PERSONAL.XLSB! or the addin’s name before every function in your personal macro workbook or addin, you can create a reference to PERSONAL.XLSB or the addin. 1 . To create the reference make sure the workbook you want to use the function in is open. 2 . Open the VB Editor (Alt + F11) 3 . Click on the name of the workbook in the Project Explorer, then click on the Tools menu, and References. 4 . Scroll down to find the name that you gave to your addin or find PERSONAL.XLSB, check/tick the box beside it, and click on OK. Your workbook should now have a new reference created to your PERSONAL.XLSB and you can use the UDF without adding the name of the containing workbook. If you want to look at a more complicated UDF then there’s a more advanced Function which displays numbers in a written text format to look at here
User Defined Functions in Excel

Page Title

Making Excel work for you.
Basic Invoice Template
Option Explicit  Function Area(Length As Double, Optional Width As Double)   If IsMissing(Width) Then Width = Length         Area = Length * Width End Function  Excel Function to Calculate Area
If you find this article useful You can help maintain my site by donating. Simply click the image below
Buy me a coffeeBuy me a coffee

© Ut duis incididunt ex officia

.

What is a User Defined Function?

User Defined Functions or UDFs are Excel functions whose properties are custom defined by the user. They can be tailored to specific user needs and greatly improve productivity as they can help you do things for which there are no built-in Excel functions. In other words, UDFs allow you to create your own functions, with your own pre-defined parameters and options. Let’s take a closer look at how this works and how you can create your own custom formulas.

How to Create User Defined Functions

You create User Defined Functions much like Macros. First you need to open the VB Editor window and add a module. Press the Alt + F11 key combination when your workbook is open. Alternatively, you can click the Visual Basic icon in the Code Group of the Developer Tab of the Ribbon In the VBA window, go to Insert -> Module. Now you are ready to start writing your first UDF. Here’s a simple one to calculate the area of a rectangle or square. We are going to need a procedure to create the User Defined Functions. Procedures are defined as code blocks that can run multiple times. There are 2 types of procedures: 1 . Subroutines or Sub 2 . Functions The main difference between the two is how they return a value. A subroutines only runs the code, but a function can return a value as well. It is a Function that we need to create the UDF. A function should start with a Function statement and have a name, and end with an End Function statement. All codes should go into these statements. Subroutines, on the other hand, use Sub and End Sub statements. For obvious reasons let’s call our Function Area. Remember there are certain rules to follow similar to creating a macro or subprocedure. The name of the function cannot have spaces in it, you can replace a space with an underscore. You cannot name a function with the name of a cell reference. For example, you can not name the function A1 as it also refers to a cell in Excel worksheet. Your function should not the same name as that of an existing Excel Function. If you do this, Excel would give preference to the in-built function.

Argument(s)

A Function can work without any arguments, similar to the NOW, or TODAY() Functions. To use a function with argument(s), they should be defined in the code as well. Arguments are defined next to the function’s name, between brackets. Argument names can be used as constants in the code. The arguments of your functions can be made optional. A common example is the VLOOKUP function which can take 3 or 4 arguments. The last argument, range_lookup, is an optional argument that you can choose to omit, if you intend to use it’s default value. If you omit the last argument of the VLOOKUP function then it takes this as TRUE (or 1) and perform the calculations accordingly. The same logic can be used with a UDF as well. To make an argument optional, you should use the Optional statement before the argument name and define its default value. As you can see the sample Function takes two Arguments to return a Double. The second is set as Optional which means that you do not need to use it. In this case if it is missing the Function assigns it the same value as the Length, i.e. do this when the shape is a square. It’s also a good practice to specify what kind of argument the function expects. In this example, because we will be using lengths which may be ecimalised, we can use Double type. If you don’t specify a data type, VBA would consider it to be a variant (which means you can use any data type). Note that the Function is specified as a Double data type as well. This will tell Excel that the result of the formula will be a Double data type. You can now use your Function as you would any other Function by adding it to a cell =Area(10.5,6) will return the area of a rectangle of those dimensions, i.e 56.

Save and distribute

Now that you have written and tested your function, the next step is saving it for future use or even send them to another user. First of all, a UDF is VBA code. You have a few options: You can save the file as an XLSM file which means an Excel file with macros. This makes the UDF specific to that file. You cannot use your custom function in other files. if you want to use it with all workbooks or pass onto a colleague or friend then you can save your custom functions in an add-in file, XLAM. You can think of add in files as files containing VBA code that run in the background. You can set an add-in file to open automatically with Excel. So, you can use your functions every time. read my addins page for more details. Similarly, you can add the Function to your PERSONAL.xlsb file which is a personal macro workbook that you can save your macros in and use with every workbook that is open. Because UDFs are macros, you can save your functions into your Personal.xlsb as well. If you use an addin or PERSONAL.xlsb to store your addin then you should use Personal.xlsb or the addin’s namewhen calling your function, e.g if you save the Area function into your Personal.xlsb, you need to call the function like this: =Personal.xlsb!Area(10.5,6) To avoid having to type PERSONAL.XLSB! or the addin’s name before every function in your personal macro workbook or addin, you can create a reference to PERSONAL.XLSB or the addin. 1 . To create the reference make sure the workbook you want to use the function in is open. 2 . Open the VB Editor (Alt + F11) 3 . Click on the name of the workbook in the Project Explorer, then click on the Tools menu, and References. 4 . Scroll down to find the name that you gave to your addin or find PERSONAL.XLSB, check/tick the box beside it, and click on OK. Your workbook should now have a new reference created to your PERSONAL.XLSB and you can use the UDF without adding the name of the containing workbook. If you want to look at a more complicated UDF then there’s a more advanced Function which displays numbers in a written text format to look at here
User Defined Functions in Excel

Page Title

Logotype