{"id":12194,"date":"2014-05-04T20:58:49","date_gmt":"2014-05-04T20:58:49","guid":{"rendered":"http:\/\/www.learnexcelmacro.com\/wp\/?p=3601"},"modified":"2018-09-21T08:44:42","modified_gmt":"2018-09-21T08:44:42","slug":"how-to-create-custom-functions-in-excel","status":"publish","type":"post","link":"https:\/\/vmlogger.com\/excel\/2014\/05\/how-to-create-custom-functions-in-excel\/","title":{"rendered":"Create UDF in Excel in 4 Steps – Your Own Excel Formula"},"content":{"rendered":"U<\/span>\n

ser Defined functions (UDF) is also known as Custom Functions (formulas) in Excel. So far, I have created many different User Defined Functions<\/a> for different purposes but never told you about How to create Custom Functions in Excel<\/strong> in a systematic way.. Therefore in this article I am going to teach you Step by step procedure to create your own Custom formula in Excel. <\/p>\n

What is User Defined Function in Excel (UDF) ? <\/h1>\n

With the name it is very clear that this is a function which is defined by users \ud83d\ude00 . Though, Excel has thousands of Built-in formulas which makes our job lot easier. Still, sometimes while working we feel..I wish… I had a simple excel formula to do this. Your wish come true by this feature of Excel called – UDF (User Defined Function).
\nTherefore such functions which are defined by users for their own tasks, are called as User Defined Functions (UDF)<\/strong>. They are also called Custom Functions.<\/strong>. Once they are defined in your workbook, they exactly work like a Built-in formula of Excel.<\/em><\/strong><\/p>\n

4 Simple Steps to create User Defined Functions in Excel<\/h1>\n

Follow the below steps to create your own User Defined Function:<\/p>\n

Step 1.<\/h2>\n

Open your Workbook<\/p>\n

Step 2.<\/h2>\n

Press ALT+F11<\/strong> to Open VBE (Visual Basic Editor) of the Workbook<\/p>\n

Step 3.<\/h2>\n

Add a Module as shown below
\n 
\n\"How
\n <\/p>\n

Step 4.<\/h2>\n

This is the step where exactly you are going to write your function.
\nYour function should have the following format:<\/p>\n

\n

Public Function<\/strong> FunctionName<\/i> (<\/strong>parameter1 As type1, parameter2 As type2,….<\/i>) As<\/strong> returnType<\/i>
\n….. lines of code to calculate result<\/em>
\nFunctionName<\/i> = result
\nEnd Function<\/strong>\n<\/div>\n

Important Points to Note<\/h1>\n

While writing your function make sure you follow below points:<\/p>\n

\n

1. A User Defined function should Start with a FUNCTION<\/strong> Statement
\n2. Function Statement usually specifies few Parameters based on the requirement. It is not mandatory though.
\n3. DO NOT forget to assign the result of the function (which you would like to see as a result of the formula in the Cell) in to a variable name same as the Function Name. <\/p>\n

Note:<\/strong> 3rd POINT IS VERY IMPORTANT TO DISPLAY THE RESULT IN THE CELL WHERE FORMULA IS WRITTEN. IF VARIABLE NAME<\/strong> AND FUNCTION NAME<\/strong> IS NOT SAME THEN YOU WILL FIND NO RESULT IN THE CELL<\/p>\n<\/div>\n

\nYes, you are done. Now you know how to create a custom function in excel. This was theory part.. now learn it by doing it with the following example.<\/em>
\n<\/h3>\n

Example: How to Create Custom Function<\/h1>\n

I will take an example from my previous article to explain this – UDF – to get Word Count Excel Formula<\/a><\/strong>.<\/p>\n

\r\n'********************************************************\r\n'** Cell is a veriable of a Range Type. It means user  **\r\n'** is allowed to select a cell address as input.Value **\r\n'** Value of that cell is used for getting the word    **\r\n'** count.                                             **\r\n'**  -----------------------------------------------   **\r\n'** Result of this Function is passed in a variable    **\r\n'** of the same name as the Function - GetWordCount    **\r\n'********************************************************\r\nPublic Function GetWordCount(Cell As Range) As Integer\r\n    GetWordCount = UBound(VBA.Split(Cell.Value, " ")) + 1\r\nEnd Function\r\n<\/code><\/pre>\n
Explanation : How it Works ? <\/h1>\n
1. As soon as you type =GetWordCount<\/strong> in any cell of the same Workbook then excel will look in to the module, if there is any function defined with the same name. Excel will display the function name as soon as you start typing the Function Name as shown in beloe picture:
\n 
\n\"UDF
\n 
\n2. Once found then this function will get triggered.
\n3. Result of the function will be shown in the cell ONLY IF the variable name is same as the Function. This is important. Otherwise this function will be executed and result will be calculated but user will not be able to see any result in the cell.<\/p>\n
<\/a><\/p>\n
List of User Defined Functions Created<\/h1>\n
You can read about some more examples and Downloadable files about User Defined Function<\/p>\n
\n
\n
 <\/i>   UDF To check if File is Open?<\/a><\/h2>\n
<\/i>    UDF to Convert Currency to Words<\/a><\/h2>\n
 <\/i>   UDF to Count Words in Cell<\/a><\/h2>\n
 <\/i>   UDF to Extract Numbers from a cell<\/a><\/h2>\n
 <\/i>   UDF to Extract Special Characters from a cell<\/a><\/h2>\n
 <\/i>   UDF to Extract Alphabets from a cell<\/a><\/h2>\n
 <\/i>   UDF to Strip\/Remove HTML tags<\/a><\/h2>\n
 <\/i>   UDF to Extract email ID from a Cell<\/a><\/h2>\n<\/div>\n<\/div>\n<\/span>","protected":false},"excerpt":{"rendered":"
ser Defined functions (UDF) is also known as Custom Functions (formulas) in Excel. So far, I have created many different User Defined Functions for different purposes but never told you about How to create Custom Functions in Excel in a systematic way.. Therefore in this article I am going to teach you Step by step […]<\/p>\n","protected":false},"author":45,"featured_media":14279,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_et_pb_use_builder":"","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[1672,1673,1246,2056],"tags":[],"yoast_head":"\nCreate UDF in Excel in 4 Steps - Your Own Excel Formula - Let's excel in Excel<\/title>\n<meta name=\"description\" content=\"What is User Defined Function in Excel? How to create User Defined Function? What is Custom Function? List of Useful UDFs in Excel. UDF- explained in Detail. Step by Step procedure to create UDF. Create UDF in 4 steps. UDF in Excel VBA. Create your own formula in Excel. Steps to create your own excel formula\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/vmlogger.com\/excel\/2014\/05\/how-to-create-custom-functions-in-excel\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Create UDF in Excel in 4 Steps - Your Own Excel Formula\" \/>\n<meta property=\"og:description\" content=\"What is User Defined Function in Excel? How to create User Defined Function? What is Custom Function? List of Useful UDFs in Excel. UDF- explained in Detail. Step by Step procedure to create UDF. Create UDF in 4 steps. UDF in Excel VBA. Create your own formula in Excel. Steps to create your own excel formula\" \/>\n<meta property=\"og:url\" content=\"https:\/\/vmlogger.com\/excel\/2014\/05\/how-to-create-custom-functions-in-excel\/\" \/>\n<meta property=\"og:site_name\" content=\"Let's excel in Excel\" \/>\n<meta property=\"article:publisher\" content=\"http:\/\/www.facebook.com\/vmlogger\" \/>\n<meta property=\"article:author\" content=\"http:\/\/www.facebook.com\/vmlogger\" \/>\n<meta property=\"article:published_time\" content=\"2014-05-04T20:58:49+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2018-09-21T08:44:42+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2017\/09\/how-to-create-udf-5.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"800\" \/>\n\t<meta property=\"og:image:height\" content=\"538\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Vishwamitra Mishra\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@https:\/\/www.twitter.com\/learnexcelmacro\" \/>\n<meta name=\"twitter:site\" content=\"@learnexcelmacro\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Vishwamitra Mishra\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2014\/05\/how-to-create-custom-functions-in-excel\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2014\/05\/how-to-create-custom-functions-in-excel\/\"},\"author\":{\"name\":\"Vishwamitra Mishra\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"headline\":\"Create UDF in Excel in 4 Steps – Your Own Excel Formula\",\"datePublished\":\"2014-05-04T20:58:49+00:00\",\"dateModified\":\"2018-09-21T08:44:42+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2014\/05\/how-to-create-custom-functions-in-excel\/\"},\"wordCount\":655,\"commentCount\":4,\"publisher\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"articleSection\":[\"Excel Formula\",\"Excel Functions\",\"Excel Macro\",\"User Defined Function\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/vmlogger.com\/excel\/2014\/05\/how-to-create-custom-functions-in-excel\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2014\/05\/how-to-create-custom-functions-in-excel\/\",\"url\":\"https:\/\/vmlogger.com\/excel\/2014\/05\/how-to-create-custom-functions-in-excel\/\",\"name\":\"Create UDF in Excel in 4 Steps - Your Own Excel Formula - Let's excel in Excel\",\"isPartOf\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#website\"},\"datePublished\":\"2014-05-04T20:58:49+00:00\",\"dateModified\":\"2018-09-21T08:44:42+00:00\",\"description\":\"What is User Defined Function in Excel? How to create User Defined Function? What is Custom Function? List of Useful UDFs in Excel. UDF- explained in Detail. Step by Step procedure to create UDF. Create UDF in 4 steps. UDF in Excel VBA. Create your own formula in Excel. Steps to create your own excel formula\",\"breadcrumb\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2014\/05\/how-to-create-custom-functions-in-excel\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/vmlogger.com\/excel\/2014\/05\/how-to-create-custom-functions-in-excel\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2014\/05\/how-to-create-custom-functions-in-excel\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/vmlogger.com\/excel\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Excel Formula\",\"item\":\"https:\/\/vmlogger.com\/excel\/excel-formula\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Create UDF in Excel in 4 Steps – Your Own Excel Formula\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#website\",\"url\":\"https:\/\/vmlogger.com\/excel\/\",\"name\":\"Let's excel in Excel\",\"description\":\"Let's share knowledge\",\"publisher\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/vmlogger.com\/excel\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\",\"name\":\"Vishwamitra Mishra\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png\",\"contentUrl\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png\",\"width\":528,\"height\":560,\"caption\":\"Vishwamitra Mishra\"},\"logo\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/\"},\"description\":\"My name is Vishwamitra Mishra. Friends Call me Vishwa. I hold a Bachelor\u2019s Degree in Computer Science from D.A.V.V. Indore & currently working as a Technical Lead having over 7 years of experience.\",\"sameAs\":[\"http:\/\/www.learnexcelmacro.com\",\"http:\/\/www.facebook.com\/vmlogger\",\"https:\/\/twitter.com\/https:\/\/www.twitter.com\/learnexcelmacro\",\"https:\/\/www.youtube.com\/c\/VMLogger\"],\"url\":\"https:\/\/vmlogger.com\/excel\/author\/vishwamitra\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Create UDF in Excel in 4 Steps - Your Own Excel Formula - Let's excel in Excel","description":"What is User Defined Function in Excel? How to create User Defined Function? What is Custom Function? List of Useful UDFs in Excel. UDF- explained in Detail. Step by Step procedure to create UDF. Create UDF in 4 steps. UDF in Excel VBA. Create your own formula in Excel. Steps to create your own excel formula","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/vmlogger.com\/excel\/2014\/05\/how-to-create-custom-functions-in-excel\/","og_locale":"en_US","og_type":"article","og_title":"Create UDF in Excel in 4 Steps - Your Own Excel Formula","og_description":"What is User Defined Function in Excel? How to create User Defined Function? What is Custom Function? List of Useful UDFs in Excel. UDF- explained in Detail. Step by Step procedure to create UDF. Create UDF in 4 steps. UDF in Excel VBA. Create your own formula in Excel. Steps to create your own excel formula","og_url":"https:\/\/vmlogger.com\/excel\/2014\/05\/how-to-create-custom-functions-in-excel\/","og_site_name":"Let's excel in Excel","article_publisher":"http:\/\/www.facebook.com\/vmlogger","article_author":"http:\/\/www.facebook.com\/vmlogger","article_published_time":"2014-05-04T20:58:49+00:00","article_modified_time":"2018-09-21T08:44:42+00:00","og_image":[{"width":800,"height":538,"url":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2017\/09\/how-to-create-udf-5.jpg","type":"image\/jpeg"}],"author":"Vishwamitra Mishra","twitter_card":"summary_large_image","twitter_creator":"@https:\/\/www.twitter.com\/learnexcelmacro","twitter_site":"@learnexcelmacro","twitter_misc":{"Written by":"Vishwamitra Mishra","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/vmlogger.com\/excel\/2014\/05\/how-to-create-custom-functions-in-excel\/#article","isPartOf":{"@id":"https:\/\/vmlogger.com\/excel\/2014\/05\/how-to-create-custom-functions-in-excel\/"},"author":{"name":"Vishwamitra Mishra","@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"headline":"Create UDF in Excel in 4 Steps – Your Own Excel Formula","datePublished":"2014-05-04T20:58:49+00:00","dateModified":"2018-09-21T08:44:42+00:00","mainEntityOfPage":{"@id":"https:\/\/vmlogger.com\/excel\/2014\/05\/how-to-create-custom-functions-in-excel\/"},"wordCount":655,"commentCount":4,"publisher":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"articleSection":["Excel Formula","Excel Functions","Excel Macro","User Defined Function"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/vmlogger.com\/excel\/2014\/05\/how-to-create-custom-functions-in-excel\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/vmlogger.com\/excel\/2014\/05\/how-to-create-custom-functions-in-excel\/","url":"https:\/\/vmlogger.com\/excel\/2014\/05\/how-to-create-custom-functions-in-excel\/","name":"Create UDF in Excel in 4 Steps - Your Own Excel Formula - Let's excel in Excel","isPartOf":{"@id":"https:\/\/vmlogger.com\/excel\/#website"},"datePublished":"2014-05-04T20:58:49+00:00","dateModified":"2018-09-21T08:44:42+00:00","description":"What is User Defined Function in Excel? How to create User Defined Function? What is Custom Function? List of Useful UDFs in Excel. UDF- explained in Detail. Step by Step procedure to create UDF. Create UDF in 4 steps. UDF in Excel VBA. Create your own formula in Excel. Steps to create your own excel formula","breadcrumb":{"@id":"https:\/\/vmlogger.com\/excel\/2014\/05\/how-to-create-custom-functions-in-excel\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/vmlogger.com\/excel\/2014\/05\/how-to-create-custom-functions-in-excel\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/vmlogger.com\/excel\/2014\/05\/how-to-create-custom-functions-in-excel\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/vmlogger.com\/excel\/"},{"@type":"ListItem","position":2,"name":"Excel Formula","item":"https:\/\/vmlogger.com\/excel\/excel-formula\/"},{"@type":"ListItem","position":3,"name":"Create UDF in Excel in 4 Steps – Your Own Excel Formula"}]},{"@type":"WebSite","@id":"https:\/\/vmlogger.com\/excel\/#website","url":"https:\/\/vmlogger.com\/excel\/","name":"Let's excel in Excel","description":"Let's share knowledge","publisher":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/vmlogger.com\/excel\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5","name":"Vishwamitra Mishra","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/","url":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png","contentUrl":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png","width":528,"height":560,"caption":"Vishwamitra Mishra"},"logo":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/"},"description":"My name is Vishwamitra Mishra. Friends Call me Vishwa. I hold a Bachelor\u2019s Degree in Computer Science from D.A.V.V. Indore & currently working as a Technical Lead having over 7 years of experience.","sameAs":["http:\/\/www.learnexcelmacro.com","http:\/\/www.facebook.com\/vmlogger","https:\/\/twitter.com\/https:\/\/www.twitter.com\/learnexcelmacro","https:\/\/www.youtube.com\/c\/VMLogger"],"url":"https:\/\/vmlogger.com\/excel\/author\/vishwamitra\/"}]}},"_links":{"self":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts\/12194"}],"collection":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/users\/45"}],"replies":[{"embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/comments?post=12194"}],"version-history":[{"count":0,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts\/12194\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/media\/14279"}],"wp:attachment":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/media?parent=12194"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/categories?post=12194"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/tags?post=12194"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}