{"text_format":"1","workbook":"xlsx","n_sheets":1,"sheets":["Examples"],"not_rendered":{"charts":0,"images":3,"macros":1,"external_links":0,"defined_names":1,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Examples","index":0,"n_rows":80,"n_cols":13,"rows_kept":80,"cut":false,"hidden":false,"merged":[[47,5,48,13],[43,5,43,8],[54,5,54,8],[58,5,59,13],[64,5,64,8],[61,5,61,13],[69,5,69,8]]}
[null,null,null,null,"Custom User Defined Function Examples"]
[null,null,null,null,"https://www.vertex42.com/blog/excel-formulas/custom-user-defined-functions.html",null,null,null,null,null,null,null,"© 2017-2023 Vertex42 LLC"]
[]
[null,null,null,null,"This workbook contains examples from the article \"How to Create Custom User Defined Functions in Excel\" by Jon Wittwer."]
[null,null,null,null,"To view the VBA code used for these custom functions, open VBA (Alt+F11) and open the CustomFunctions module."]
[]
[]
[null,null,null,null,null,"EXAMPLES"]
[]
[]
[null,null,null,null,"Example 1: Get the Address of a Hyperlink"]
[]
[null,null,null,null,"Syntax:","=LinkAddress(cell,[default_value])"]
[]
[null,null,null,null,"Hyperlink:","Click Here to go to Vertex42.com"]
[null,null,null,null,"Result:",{"$f":"LinkAddress(F15)","v":"https://www.vertex42.com/"}]
[]
[]
[null,null,null,null,"Example 2: Extract the Nth Element from a String"]
[]
[null,null,null,null,"Syntax:","=GetElement(text, n, delimiter)"]
[]
[null,null,null,null,null,null,null,null,null,null,"Text","n","Result"]
[null,null,null,null,"Formula:","=GetElement(text,3,\"-\")",null,null,null,null,"1-800-333-4444",3,{"$f":"GetElement(K24,L24,\"-\")","v":"333"}]
[]
[null,null,null,null,"Office 365!","With the TEXTSPLIT function in Office 365, this can now be done easily with a regular formula:"]
[null,null,null,null,null,null,null,null,null,null,"Text","n","Result"]
[null,null,null,null,"Formula:","=INDEX(TEXTSPLIT(text,,delimiter),n)",null,null,null,null,"1-800-333-4444",3,{"$f":"INDEX(_xlfn.TEXTSPLIT(K28,,\"-\"),L28)","v":"333"}]
[]
[]
[null,null,null,null,"Example 3: Return the name of a month"]
[]
[]
[null,null,null,null,null,null,null,null,null,null,null,"Month","Result"]
[null,null,null,null,"Formula:","=VBA_MonthName(month)",null,null,null,null,null,3,{"$f":"VBA_MonthName(L35)","v":"March"}]
[null,null,null,null,"Formula:","=VBA_MonthName(month, boolean_abbreviate)",null,null,null,null,null,4,{"$f":"VBA_MonthName(L36,TRUE)","v":"Apr"}]
[]
[]
[]
[null,null,null,null,"Example 4: Calculate Age for Dates Prior to 1900"]
[]
[]
[null,null,null,null,null,null,null,null,null,null,"Start Date","End Date","Years"]
[null,null,null,null,"Formula:","=AgeInYears(start_date,end_date)",null,null,null,null,"10-Oct-1850","1910-01-05",{"$f":"AgeInYears(K44,L44)","v":59}]
[null,null,null,null,null,null,null,null,null,null,"2016-02-29","2017-02-28",{"$f":"AgeInYears(K45,L45)","v":0}]
[null,null,null,null,null,null,null,null,null,null,"1/1/1800","1/1/1801",{"$f":"AgeInYears(K46,L46)","v":1}]
[]
[null,null,null,null,"NOTE","This custom function is designed to work like the =DATEDIF(start,end,\"y\") formula, except that it can handle pre-1900 dates."]
[]
[]
[null,null,null,null,"Example 5: Get the Color of a Cell (Fill Color)"]
[]
[]
[null,null,null,null,null,null,null,null,null,null,"Cell","Result"]
[null,null,null,null,"Formula:","=GetCellColor(cell_ref)",null,null,null,null,null,{"$f":"GetCellColor(K55)","v":55}]
[null,null,null,null,null,null,null,null,null,null,null,{"$f":"GetCellColor(K56)","v":55}]
[null,null,null,null,null,null,null,null,null,null,null,{"$f":"GetCellColor(K57)","v":1}]
[]
[null,null,null,null,"NOTE","This isn't exact, because it is based on the cell.Interior.IndexColor which matches the color to the closest color in the old color palette (File > Options > Save > Colors)."]
[]
[]
[null,null,null,null,null,"The following two functions may be more useful because they return the actual HEX or RGB color codes for the fill color."]
[null,null,null,null,null,null,null,null,null,null,"Cell","HEX Result"]
[null,null,null,null,null,null,null,null,null,null,"#2A5181",{"$f":"GetHexColor(K64)","v":"#2A5181"}]
[null,null,null,null,null,"=GetHexColor(cell_ref)",null,null,null,null,"#513298",{"$f":"GetHexColor(K65)","v":"#513298"}]
[null,null,null,null,null,null,null,null,null,null,"#000000",{"$f":"GetHexColor(K66)","v":"#000000"}]
[]
[null,null,null,null,null,null,null,null,null,null,"Cell","RGB Result"]
[null,null,null,null,null,null,null,null,null,null,null,{"$f":"GetRGBColor(K69)","v":"42,81,129"}]
[null,null,null,null,null,"=GetRGBColor(cell_ref)",null,null,null,null,null,{"$f":"GetRGBColor(K70)","v":"81,50,152"}]
[null,null,null,null,null,null,null,null,null,null,null,{"$f":"GetRGBColor(K71)","v":"0,0,0"}]
[]
[]
[]
[]
[null,null,null,null,null,"REFERENCES"]
[]
[null,null,null,null,"ARTICLE","msdn.microsoft.com: Application.Volatile Method"]
[]
[null,null,null,null,"ARTICLE","excelribbon.tips.net: Determining the RGB Value of a Color"]