How to Create Custom User Defined Excel Functions
Excel allows you to create custom functions, called "User Defined Functions" (UDFs) that can be used the same way you would use SUM() or some other built-in Excel function. This can be especially useful for advanced mathematics or special text manipulation or date calculations prior to 1900. Many Excel add-ins provide large collections of specialized functions.
This article will help you get started creating user defined functions with a few useful examples.
Hot to Create a User Defined Function
- Open a new Excel workbook.
- Get into VBA (Press Alt+F11)
- Insert a new module (Insert > Module)
- Copy and Paste the Excel user defined function examples
- Get out of VBA (Press Alt+Q)
- Use the functions - They will appear in the Paste Function dialog box (Shift+F3) under the "User Defined" category
If you want to use a UDF in more than one workbook, you can save your functions in your own custom add-in. Simply save your excel file that contains your VBA functions as an add-in file (.xla for Excel 2003 or .xlam for Excel 2007+). Then load the add-in (Tools > Add-Ins... for Excel 2003 or Developer > Add-Ins for Excel 2010+).
Warning! Be careful about using custom functions in spreadsheets that you need to share with others. If they don't have your add-in, the functions will not work when they use the spreadsheet.
Benefits of User Defined Excel Functions
- Create a complex or custom math function.
- Simplify formulas that would otherwise be extremely long "mega formulas".
- Diagnostics such as checking cell formats.
- Custom text manipulation.
- Advanced array formulas and matrix functions.
- Date calculations prior to 1900 using the built-in VBA date functions.
Limitations of UDF's
- Cannot "record" an Excel UDF like you can an Excel macro.
- More limited than regular VBA macros. UDF's cannot alter the structure or format of a worksheet or cell.
- If you call another function or macro from a UDF, the other macro is under the same limitations as the UDF.
- Cannot place a value in a cell other than the cell (or range) containing the formula. In other words, UDF's are meant to be used as "formulas", not necessarily "macros".
- Excel user defined functions in VBA are usually much slower than functions compiled in C++ or FORTRAN.
- Often difficult to track errors.
- If you create an add-in containing your UDF's, you may forget that you have used a custom function, making the file less sharable.
- Adding user defined functions to your workbook will trigger the "macro" flag (a security issue: Tools > Macros > Security...).
User Defined Function Examples
To see the following examples in action, download the file below. This file contains the VBA custom functions, so after opening it you will need to enable macros.
Example #1: Get the Address of a Hyperlink
The following example can be useful when extracting hyperlinks from tables of links that have been copied into Excel, when doing post-processing on Excel web queries, or getting the email address from a list of "mailto:" hyperlinks.
This function is also an example of how to use an optional Excel UDF argument. The syntax for this custom Excel function is:
To see an example of how to work with optional arguments, look up the IsMissing command in Excel's VBA help files (F1).
Function LinkAddress(cell As range, _ Optional default_value As Variant) 'Lists the Hyperlink Address for a Given Cell 'If cell does not contain a hyperlink, return default_value If (cell.range("A1").Hyperlinks.Count <> 1) Then LinkAddress = default_value Else LinkAddress = cell.range("A1").Hyperlinks(1).Address End If End Function
Example #2: Extract the Nth Element From a String
This example shows how to take advantage of some functions available in VBA in order to do some slick text manipulation. What if you had a bunch of telephone numbers in the following format: 1-800-999-9999 and you wanted to pull out just the 3-digit prefix?
This UDF takes as arguments the text string, the number of the element you want to grab (n), and the delimiter as a string (eg. "-"). The syntax for this example user defined function in Excel is:
Example: If B3 contains "1-800-333-4444", and cell C3 contains the formula, =GetElement(B3,3,"-"), C3 will then equal "333". To turn the "333" into a number, you would use =VALUE(GetElement(B3,3,"-")).
Function GetElement(text As Variant, n As Integer, _ delimiter As String) As String GetElement = Split(text, delimiter)(n - 1) End Function
Example #3: Return the name of a month
The following function is based on the built-in visual basic MonthName() function and returns the full name of the month given the month number. If the second argument is TRUE, it will return the abbreviation.
Example: =VBA_MonthName(3) will return "March" and =VBA_MonthName(3,TRUE) will return "Mar".
Function VBA_MonthName(themonth As Long, _ Optional abbreviate As Boolean) As Variant VBA_MonthName = MonthName(themonth, abbreviate) End Function
Example #4: Calculate Age for Years Prior to 1900
The Excel function DATEDIF(start_date,end_date,"y") is a very simple way to calculate the age of a person if the dates are after 1/1/1900. The VBA date functions are able to handle all the Gregorian dates, so custom functions based on the VBA functions can allow you to work with dates prior to 1900.
Example: =AgeInYears("10-Oct-1850","5-Jan-1910") returns the value 60. Interestingly, =AgeInYears("29-Feb-2016","28-Feb-2017") returns 1 year, though DATEDIF would return 0 years.
Function AgeInYears(start_date As Variant, end_date As Variant, _ Optional interval As Variant) As Variant If IsMissing(interval) Then interval = "yyyy" End If AgeInYears = DateDiff(interval, start_date, end_date) End Function
More Custom Excel Function Examples
For an excellent explanation of pretty much everything you need to know to create your own custom Excel function, I would recommend Excel 2016 Formulas. The book provides many good user defined function examples, so if you like to learn by example, it is a great resource.
- Rounding Significant Figures in Excel :: Shows how to return #NUM and #N/A error values.
- UDF Examples - www.ozgrid.com - Provides many examples of user defined functions, including random numbers, hyperlinks, count sum, sort by colors, etc.
- Build an Excel Add-In - http://www.fontstuff.com/vba/vbatut03.htm - An excellent tutorial that takes you through building an add-in for a custom excel function.