Excel

User Defined Functions (UDF)

Sometimes you need to perform a calculation that none of Excel’s built-in functions can accomplish. With VBA, you can write your own custom functions that works similarly to built-in functions. A custom function (also called User Defined Function or UDF), shows in small letter to differentiate it from the built-in Excel functions. This function can be use as a worksheet function or as part of a macro procedure.

A user defined function (UDF) returns a value to the cell it occupies, it can not do anything other than returning a value to its own cell. A custom function (UDF) can be created by writing the code into a standard module. A UDF never appears in the Macro dialog box. To create a Function, perform the following steps:

  • Press Alt + F11 to open VBE (Visual Basic Editor)
    Developer tab

  • Insert a new (or open an existing) Module. To insert a new module click Insert from the menu and click Module as shown in following figure:
    Insert a new module in VBE from the main menu

    Or right click VBAProject, choose Insert > Module, see following figure:
    Right click VBAProject to Insert Module

  • Write the code in the module (a UDF always begins with the Function statement).

Example: User-Defined Function without parameters:

Code:

Function WebSite()
 WebSite = "https://www.BrainBell.com"
End Function

A UDF always begins with the Function statement and ends with the End Function statement. You can also set a function scope by declaring the Public or Private qualifier. A Public function appears in the list of functions in the Insert Function dialog box. The Public is the default function scope in VBA if a qualifier not defined:

Insert function

We created a new User Defined Function (UDF), WebSite. To use this UDF, click Formula > Insert Function as shown in above figure, or write =WebSite() in a cell (or write =WebSite(), Excel automatically convert function name in small letters to differentiate it from built-in functions). The UDF will return https://www.BrainBell.com value, see following figure:

Returning result from a function
Function return value

To return a result from the function you must assign the result to the name of your function, in our example the function name is WebSite and the same name is used to return the value. You must use Set keyword if you want to return an Object data type from the function, for example:

'Returns a value
Function brainbell()
 brainbell = "https://www.brainbell.com"
End Function

'Returns an Object
Function myRange()
 set myRange = Range("D1")
End Function

You can also create a custom function (UDF) by specifying a single or multiple parameters.

Example: Using parameters in UDF

Function WebSite(topic)
 WebSite = "https://www.BrainBell.com/" & topic
End Function

You can add more than one parameters to a function by separating them a comma:

Function WebSite(topic, id)
 If id < 0 Then
  id = 0
 End If

 WebSite = "https://www.BrainBell.com/" & id & "/" & topic

End Function

Example: Using optional parameter in UDF

You can specify optional parameters in function. The Optional keyword is used to define an optional parameter. You must specify a default value to the optional parameter, see following example:

Function WebSite(Optional topic = "")
 WebSite = "https://www.BrainBell.com/" & topic
End Function

Assign data-type to parameters

Function WebSite(topic As String, Optional id As Integer = 0)
 WebSite = "https://www.BrainBell.com/" & topic
End Function

Assign data-type to return value

Function WebSite(topic) as String
 WebSite = "https://www.BrainBell.com/" & topic
End Function

Set ByVal or ByRef for a parameter

You can pass arguments to a function by value (ByVal). By default, Excel VBA passes arguments by reference (ByRef):

Function WebSite(ByVal topic)
 WebSite = "https://www.BrainBell.com/" & topic
End Function
  • ByVal means, a copy of the provided value will be sent to the function.
  • ByRef means, a reference to the original value will be sent to the function.

Complete example: combining all keywords:

Function WebSite(ByVal topic As String, Optional ByRef id As Integer = 0) As String

 If id < 0 Then
  id = 0
 End If

 WebSite = "https://www.BrainBell.com/" & topic & "/" & id
End Function