How to create a custom function in Excel — make user defined functions UDF using VBA language

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. 

Insert code to VBE
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.

Custom function use example

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
Please rate this article
(0 stars / 0 votes)