How to create a custom function in Excel — make user defined functions UDF using VBA language
What are custom functions
Custom functions help us to extend the functionality of calculations and data transformations in Excel. They are used in the same way as standard Excel functions, but we can set the logic of calculations ourselves.
User-defined functions take values from parameters when called, perform certain calculations with them and return the result. These parameters can be both a cell link and a static value — string or number.
Functions are created in the same way as macros - in the Excel VBA editor window. The difference is that a macro is an algorithm of actions, which itself does not return any result and can manipulate a sheet, a book, files, almost like a real user.
Functions are deprived of such capabilities and are used only for calculations or transformations of data sent to them.
You can use them:
- on an Excel sheet
- as part of a macro or other function in the VBA editor
- as a conditional formatting rule
Custom functions are slower than Excel functions, so don't overuse them. Use them only where the standard ones are either insufficient or you have to write a formula that is too difficult to maintain and comprehend.
How to create
Let's try to compose a simple Excel function to sum two numbers. Open the Visual Basic Editor with the keyboard shortcut Alt+F11
. Then create a module for the book by clicking Insert > Module
. Paste the code below into it.
Function PLUS(number1 As Long, number2 As Long) As Long
Dim result As Long
result = number1 + number2
PLUS = result
End Function
Save the book with the keyboard shortcut Ctrl+S
. Now you can use the PLUS()
function on the worksheet. To test it, go back to the worksheet and try writing =PLUS(1;2)
in a cell.
Syntax
Let's learn what code is syntactically composed of.
A function is defined with the Function
command, instead of the Sub
command of a macro.
Then comes the name. It can be specified by numbers, Cyrillic or Latin letters, underscore. Spaces should not be used. Also, the name of a custom function must not duplicate the name of a standard function, because otherwise it will simply not work.
After the name, you should specify the arguments. You can specify the data type of the arguments, but it is not necessary. The argument types correspond to the standard VBA data types. A function can have no arguments at all. Also, arguments can be mandatory and optional. We will look at these examples later.
After the arguments, you can declare the data type of the value returned by the function.
Next comes the calculation code - you can declare variables, perform operations and calculations with arguments. Before the final End Function
command, you must specify the return value by assigning it to the function name:
Function NAME()
' calculations
NAME = 2
End Function
Arguments
You can specify a few arguments, but there may not be any at all - you probably know that Excel also has standard functions without arguments, like TODAY()
or RAND()
. For example, we can get the name of the active sheet - this requires no arguments.
Function GETSHEETNAME()
GETSHEETNAME = Application.ActiveSheet.Name
End Function
If you have arguments, you can use them inside the function as normal variables.
Optional arguments
Arguments can be optional. This is useful, for example, if a function should have a feature that is rarely used. So that we don't have to spend time writing down the argument responsible for it every time, we can set it as optional.
In our addition example, let's add the ability to print the units of measure after the number if the units argument is given.
Function SUMWITHUNIT(number1 As Long, number2 As Long, Optional unit As String)
Dim count As Long
count = number1 + number2 ' sum
If Len(ed) > 0 Then
result = count & unit ' If the units of measure are specified, add them
Else
result = count ' Or else we return just a number
End If
SUMWITHUNIT = result
End Function
Note how the optional argument is specified - the Optional
keyword is specified before the name and it is placed after the required arguments, this is important.
Default value
The optional argument also allows you to set a default value and not specify it when calling. For example, let's write a function to calculate the amount with tax, and set the default tax to 20%:
Function WITHTAX(price As Long, Optional tax As Long = 20) As Long
Dim result As Long
result = price + price * tax / 100
WITHTAX = result
End Function
The default value is specified after the argument data type using the =
symbol. It makes sense to specify the default value only for optional arguments, because the others must always be written when calling the function.
Examples
Here are some useful functions that are not originally in Excel. They can easily be implemented with standard functions as well, these are just for examples. Calculate a percentage of a number:
Function PERCENTAGE(num1 As Long, num2 As Long) As Double
Dim result As Double
result = num1 / num2 * 100
PERCENTAGE = result
End Function