How to Create a User Defined Function in Microsoft Excel :Tutorial
We generally use the built-in functions of Microsoft Excel. Sometimes, we want to use a different function that is not available in Excel. Do not worry, we can create a custom function for our needs. In this video, we will create a Word Count user defined function. Let’s learn, how we can do it.
1. Open an Excel workbook.
2. Press the Control + N key to open a new spreadsheet.
3. You can open your existing excel spreadsheet or Type your text in the spreadsheet rows for Word Count.
4. Press the Alt + F11 keys on your keyboard. This will open the Visual Basic Editor.
5. Click on the Insert menu and then click on Module from the drop-down list.
6. Let’s start creating a custom function of Word Count. Type the following code as shown in this video in the Module code window.
Function WordCount (rng As Range)
WordCount = Len(Application.WorksheetFunction.Trim(rng.Value)) -Len(Replace(rng.Value, " ", "")) + 1
End Function
7. Click on the Save Floppy disk icon.
8. Give a name to your file and select the file type Excel Macro Enabled Workbook from Save as type drop-down list, and then click on the Save button.
9. Click on the File menu and then click on the Close and Return to Microsoft Excel option.
10. Now, click on an empty cell next to the typed text cell.
11. Type =WordCount and select the function as shown from the suggestion and then click on the cell for that you want to calculate the word count.
12. Type Close parenthesis, and click on the tick mark in the function bar.
13. Click the right bottom corner of the used formula cell and drag it down to select the range of rows to calculate the word count.
14. This will show you the result of word count by using the User Defined Functions in Excel.
15. Let's remove some words from this row number 4 and see the total word count has been changed.
See, this wasn’t hard at all. Thanks for watching!
Please subscribe to our channel.
Disclaimer: Content in this video is provided on an "as is" basis with no express or implied warranties whatsoever. Any reference of any third party logos, brand names, trademarks, services marks, trade names, trade dress and copyrights shall not imply any affiliation to such third parties, unless expressly specified
