{"id":3401,"date":"2017-08-24T12:45:38","date_gmt":"2017-08-24T18:45:38","guid":{"rendered":"https:\/\/www.vertex42.com\/blog\/?p=3401"},"modified":"2024-02-02T17:13:20","modified_gmt":"2024-02-02T23:13:20","slug":"custom-user-defined-functions","status":"publish","type":"post","link":"https:\/\/www.vertex42.com\/blog\/excel-formulas\/custom-user-defined-functions.html","title":{"rendered":"How to Create Custom User Defined Functions in Excel"},"content":{"rendered":"<div class=\"wp-post-image\" itemprop=\"image\" itemscope itemtype=\"https:\/\/schema.org\/ImageObject\"><img decoding=\"async\" width=\"300\" height=\"240\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/thumbnails\/custom-functions-in-excel.png\" alt=\"Create Custom Functions in Excel\"><meta itemprop=\"url\" content=\"https:\/\/cdn.vertex42.com\/blog\/images\/thumbnails\/custom-functions-in-excel.png\"><meta itemprop=\"width\" content=\"300\"><meta itemprop=\"height\" content=\"240\"><\/div>\n<p>Excel allows you to <b>create custom functions<\/b> using VBA, called &quot;<i>User Defined Functions<\/i>&quot; (UDFs) that can be used the same way you would use SUM() or other built-in Excel functions. They can be especially useful for <b>advanced mathematics<\/b> or special <b>text manipulation<\/b> or <b>date calculations prior to 1900<\/b>. Many Excel add-ins provide large collections of specialized functions.<\/p>\n<p>This article will help you get started creating user defined functions with a few useful examples.<\/p>\n<div class=\"contents\">\n<p>This Page (contents):<\/p>\n<ul>\n<li><a href=\"#how-to-create\">How to Create a Custom User Defined Function<\/a><\/li>\n<li><a href=\"#benefits\">Benefits of User Defined Excel Functions<\/a><\/li>\n<li><a href=\"#limitations\">Limitations of UDFs<\/a><\/li>\n<li><a href=\"#examples\">User Defined Function Examples<\/a><\/li>\n<\/ul>\n<\/div>\n<p><strong>IMPORTANT!<\/strong> The new <a href=\"https:\/\/www.vertex42.com\/lambda\/\">LAMBDA Function<\/a>, available in Microsoft 365, allows you to turn complicated worksheet formulas into custom reusable functions <strong>without VBA<\/strong>. Check out Vertex42's new <a href=\"https:\/\/www.vertex42.com\/lambda\/\">LAMBDA Library<\/a> to learn how to create and use custom functions.<\/p>\n<h2>Watch the Video<\/h2>\n<div itemprop=\"video\" itemscope itemtype=\"http:\/\/schema.org\/VideoObject\"><iframe title=\"Watch the Video\" width=\"720\" height=\"405\" src=\"https:\/\/www.youtube.com\/embed\/DwwqL7egGfQ?rel=0\" frameborder=\"0\" allowfullscreen><\/iframe><meta itemprop=\"name\" content=\"How to Create Custom Excel Functions\"><meta itemprop=\"description\" content=\"Learn how to create your own custom user-defined functions in Excel using some examples from Vertex42.com.\"><meta itemprop=\"thumbnailURL\" content=\"https:\/\/cdn.vertex42.com\/blog\/images\/thumbnails\/video-custom-user-defined-functions-in-excel.png\"><meta itemprop=\"embedURL\" content=\"https:\/\/www.youtube.com\/embed\/DwwqL7egGfQ?rel=0\"><meta itemprop=\"uploadDate\" content=\"2017-09-27\"><\/div>\n<h2 id=\"how-to-create\">How to Create a Custom User Defined Function<\/h2>\n<ol>\n<li> Open a new Excel workbook.<\/li>\n<li> Get into VBA (Press <b>Alt+F11<\/b>)<\/li>\n<li> Insert a new module (<b>Insert &gt; Module<\/b>)<\/li>\n<li> Copy and Paste the Excel user defined function examples<\/li>\n<li> Get out of VBA (Press <b>Alt+Q<\/b>)<\/li>\n<li> Use the functions - They will appear in the Paste Function dialog box (<b>Shift+F3<\/b>) under the \"User Defined\" category<\/li>\n<\/ol>\n<p>If you want to use a UDF in more than one workbook, you can save your functions to your personal.xlsb workbook or save them in your own custom add-in. To create an add-in, save your excel file that contains your VBA functions as an add-in file (<b>.xla<\/b> for Excel 2003 or <b>.xlam<\/b> for Excel 2007+). Then load the add-in (<b>Tools &gt; Add-Ins...<\/b> for Excel 2003 or <b>Developer &gt; Excel Add-Ins<\/b> for Excel 2010+).<\/p>\n<p><i>Warning!<\/i> 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.<\/p>\n<h2 id=\"benefits\">Benefits of User Defined Excel Functions<\/h2>\n<ul>\n<li> Create a <b>complex or custom math<\/b> function.<\/li>\n<li> Simplify formulas that would otherwise be extremely long <b>\"mega formulas\"<\/b>.<\/li>\n<li> <b>Diagnostics<\/b> such as checking cell formats.<\/li>\n<li> Custom <b>text manipulation<\/b>.<\/li>\n<li> Advanced <b>array formulas<\/b> and matrix functions.<\/li>\n<li> Date calculations prior to 1900 using the built-in VBA date functions.<\/li>\n<\/ul>\n<h2 id=\"limitations\">Limitations of UDF's<\/h2>\n<ul>\n<li> <b>Cannot \"record\" an Excel UDF<\/b> like you can an Excel macro.<\/li>\n<li> More limited than regular VBA macros. UDF's <b>cannot alter the structure or format<\/b> of a worksheet or cell.<\/li>\n<li> If you call another function or macro from a UDF, the other macro is under the same limitations as the UDF.<\/li>\n<li> Cannot place a value in a cell other than the cell (or range) containing the formula. In other words, UDF's are <b>meant to be used as \"formulas\"<\/b>, not necessarily \"macros\".<\/li>\n<li> Excel user defined functions in VBA are usually <b>much slower<\/b> than functions compiled in C++ or FORTRAN.<\/li>\n<li> Often difficult to track errors.<\/li>\n<li> 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.<\/li>\n<li> Adding user defined functions to your workbook will <b>trigger the \"macro\" flag<\/b> (a security issue: Tools &gt; Macros &gt; Security...).<\/li>\n<\/ul>\n<h2 id=\"examples\">User Defined Function Examples<\/h2>\n<p>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.<\/p>\n<p class=\"downloadlink\"><span class=\"icon16 excel\"><\/span> <a href=\"\/Files\/examples\/CustomFunctions.xlsm\" onClick=\"ga('send','event', 'Downloads', 'Examples', 'CustomFunctions.xlsm');\" class=\"bigbtn\" rel=\"nofollow\"><b>Download the Example File<\/b><\/a> (CustomFunctions.xlsm)<\/p>\n<p><!--\n\n<p><a href=\"https:\/\/youtu.be\/DwwqL7egGfQ\" target=\"_blank\" rel=\"noopener noreferrer\">Watch the How-To Video on YouTube<\/a><\/p>\n\n--><\/p>\n<h3>Example #1: Get the Address of a Hyperlink<\/h3>\n<p>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.<\/p>\n<p>This function is also an example of how to use an <b>optional Excel UDF argument<\/b>. The syntax for this custom Excel function is:<\/p>\n<pre class=\"XLformula\">=<span class=\"function\"><b>LinkAddress<\/b><\/span>(<span class=\"arg\">cell<\/span>,[<span class=\"arg\">default_value<\/span>])<\/pre>\n<p>To see an example of how to work with <b>optional arguments<\/b>, look up the <b>IsMissing<\/b> command in Excel's VBA help files (F1).<\/p>\n<div class=\"codebox\">\n<pre><span style=\"color:blue\">Function<\/span> LinkAddress(cell <span style=\"color:blue\">As<\/span> range, _ \r\n                     Optional default_value <span style=\"color:blue\">As<\/span> <span style=\"color:blue\">Variant<\/span>) \r\n  <span style=\"color:green\">'Lists the Hyperlink Address for a Given Cell <\/span>\r\n  <span style=\"color:green\">'If cell does not contain a hyperlink, return default_value <\/span>\r\n  <span style=\"color:blue\">If<\/span> (cell.range(\"A1\").Hyperlinks.Count &lt;&gt; 1) <span style=\"color:blue\">Then<\/span> \r\n      LinkAddress = default_value \r\n  <span style=\"color:blue\">Else<\/span> \r\n      LinkAddress = cell.range(\"A1\").Hyperlinks(1).Address \r\n  <span style=\"color:blue\">End<\/span> <span style=\"color:blue\">If<\/span> \r\n<span style=\"color:blue\">End<\/span> <span style=\"color:blue\">Function<\/span><\/pre>\n<\/div>\n<h3>Example #2: Extract the Nth Element From a String<\/h3>\n<p>This example shows how to take advantage of some functions available in VBA to do some slick text manipulation. What if you had a bunch of telephone numbers in the following format: <b>1-800-999-9999<\/b> and you wanted to pull out <b>just<\/b> the 3-digit prefix?<\/p>\n<p>This UDF takes as arguments the <b>text<\/b> 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:<\/p>\n<pre class=\"XLformula\">=<span class=\"function\"><b>GetElement<\/b><\/span>(<span class=\"arg\">text<\/span>,<span class=\"arg\">n<\/span>,<span class=\"arg\">delimiter<\/span>)<\/pre>\n<p>Example: If B3 contains \"1-800-444-3333\", and cell C3 contains the formula, =GetElement(B3,3,\"-\"), C3 will then equal \"444\". To turn the \"444\" into a number, you would use =VALUE(GetElement(B3,3,\"-\")).<\/p>\n<div class=\"codebox\">\n<pre><span style=\"color:blue\">Function<\/span> GetElement(text <span style=\"color:blue\">As<\/span> <span style=\"color:blue\">Variant<\/span>, n <span style=\"color:blue\">As<\/span> <span style=\"color:blue\">Integer<\/span>, _ \r\n                    delimiter <span style=\"color:blue\">As<\/span> <span style=\"color:blue\">String<\/span>) <span style=\"color:blue\">As<\/span> <span style=\"color:blue\">String<\/span> \r\n    GetElement = <span style=\"color:blue\">Split<\/span>(text, delimiter)(n - 1)\r\n<span style=\"color:blue\">End<\/span> <span style=\"color:blue\">Function<\/span><\/pre>\n<\/div>\n<p>With the new <strong>TEXTSPLIT<\/strong> function in Microsoft 365, this formula is much easier. TEXTSPLIT can split a string into an array and then INDEX can pick the nth value.<\/p>\n<p><pre class=\"XLformula light\">\r\n=<span class=\"function\">INDEX<\/span>(<span class=\"function\">TEXTSPLIT<\/span>(<span class=\"arg\">text<\/span>,,<span class=\"green\">delimiter<\/span>),<span class=\"arg\">n<\/span>)\r\n=<span class=\"function\">INDEX<\/span>(<span class=\"function\">TEXTSPLIT<\/span>(<span class=\"green\">\"1-800-444-3333\"<\/span>,,<span class=\"green\">\"-\"<\/span>),<span class=\"green\">3<\/span>)\r\n=<span class=\"green\">444<\/span>\r\n<\/pre>\n<h3>Example #3: Return the name of a month<\/h3>\n<p>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.<\/p>\n<pre class=\"XLformula\">=<span class=\"function\"><b>VBA_MonthName<\/b><\/span>(<span class=\"arg\">month<\/span>,<span class=\"arg\">boolean_abbreviate<\/span>)<\/pre>\n<p>Example: <span class=\"mono\">=VBA_MonthName(3)<\/span> will return \"March\" and <span class=\"mono\">=VBA_MonthName(3,TRUE)<\/span> will return \"Mar.\"<\/p>\n<div class=\"codebox\">\n<pre><span style=\"color:blue\">Function<\/span> VBA_MonthName(themonth <span style=\"color:blue\">As<\/span> <span style=\"color:blue\">Long<\/span>, _\r\n   \t\t<span style=\"color:blue\">Optional<\/span> abbreviate <span style=\"color:blue\">As<\/span> <span style=\"color:blue\">Boolean<\/span>) <span style=\"color:blue\">As<\/span> <span style=\"color:blue\">Variant<\/span> \r\n    VBA_MonthName = <span style=\"color:blue\">MonthName<\/span>(themonth, abbreviate)\r\n<span style=\"color:blue\">End<\/span> <span style=\"color:blue\">Function<\/span><\/pre>\n<\/div>\n<h3>Example #4: Calculate Age for Years Prior to 1900<\/h3>\n<p>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 like Year(), Month(), Day(), DateSerial(), DateValue() 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. The function below is designed to work like DATEDIF(start_date,end_date,\"y\") as long as end_date &gt;= start_date.<\/p>\n<pre class=\"XLformula\">=<span class=\"function\"><b>AgeInYears<\/b><\/span>(<span class=\"arg\">start_date<\/span>,<span class=\"arg\">end_date<\/span>)<\/pre>\n<p>Example: <span class=\"mono\">=AgeInYears(\"10-Oct-1850\",\"5-Jan-1910\")<\/span> returns the value 59.<\/p>\n<div class=\"codebox\">\n<pre><span style=\"color:blue\">Function<\/span> AgeInYears(start_date <span style=\"color:blue\">As<\/span> <span style=\"color:blue\">Variant<\/span>, end_date <span style=\"color:blue\">As<\/span> <span style=\"color:blue\">Variant<\/span>) <span style=\"color:blue\">As<\/span> <span style=\"color:blue\">Variant<\/span> \r\n    AgeInYears = <span style=\"color:blue\">Year<\/span>(end_date) - <span style=\"color:blue\">Year<\/span>(start_date) - _\r\n    <span style=\"color:blue\">Abs<\/span>(<span style=\"color:blue\">DateSerial<\/span>(<span style=\"color:blue\">Year<\/span>(end_date), <span style=\"color:blue\">Month<\/span>(start_date), _\r\n    <span style=\"color:blue\">Day<\/span>(start_date)) > <span style=\"color:blue\">DateValue<\/span>(end_date))\r\n<span style=\"color:blue\">End<\/span> <span style=\"color:blue\">Function<\/span><\/pre>\n<\/div>\n<p><\/p>\n<h2 id=\"more-examples\">More Custom Excel Function Examples<\/h2>\n<div class=\"refblock\">\n<p>For an excellent explanation of pretty much everything you need to know to create your own custom Excel function, I would recommend <a href=\"http:\/\/www.amazon.com\/exec\/obidos\/ASIN\/1119067863\/vertex42-20\" target=\"_blank\" rel=\"noopener noreferrer\"><b><i>Excel 2016 Formulas<\/i><\/b><\/a>. The book provides <b>many good user defined function examples<\/b>, so if you like to learn by example, it is a <b>great<\/b> resource.<\/p>\n<ul>\n<li><a href=\"\/ExcelTips\/significant-figures.html\">Rounding Significant Figures in Excel<\/a> :: Shows how to return #NUM and #N\/A error values.<\/li>\n<li><a href=\"http:\/\/www.ozgrid.com\/VBA\/Functions.htm\" target=\"_blank\" rel=\"nofollow noopener noreferrer\">UDF Examples<\/a> - <i>www.ozgrid.com<\/i> - Provides many examples of user defined functions, including random numbers, hyperlinks, count sum, sort by colors, etc.<\/li>\n<li><a href=\"http:\/\/www.fontstuff.com\/vba\/vbatut03.htm\" target=\"_blank\" rel=\"nofollow noopener noreferrer\">Build an Excel Add-In<\/a> - <i>http:\/\/www.fontstuff.com\/vba\/vbatut03.htm<\/i> - An excellent tutorial that takes you through building an add-in for a custom excel function.<\/li>\n<\/ul>\n<p style=\"font-size:0.9em;\"><em>Note: I originally published most of this article in 2004, but I've updated it significantly and included other examples, as well as the download file.<\/em><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Learn how to create your own custom functions in Excel using VBA. This articles explains the pros and cons of user-defined functions and provides examples.<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":"","_links_to":"","_links_to_target":""},"categories":[79],"tags":[],"class_list":{"0":"post-3401","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-excel-formulas"},"_links":{"self":[{"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/posts\/3401","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/comments?post=3401"}],"version-history":[{"count":0,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/posts\/3401\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/media?parent=3401"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/categories?post=3401"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/tags?post=3401"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}