In this article, you will learn how to create a User Defined Function in VBA to get the frequency of specific word in a paragraph. Using this UDF you will be able to get the Total Words, Specific word count (Case Sensitive Check) and Specific word count (Non-Case Sensitive Check).
For example, we have below given paragraph, and we have got the Total word count and Specific word count also-

Below is the code for this user defined function –
Option Explicit Function Word_Count(Complete_Text As String, Optional Specific_Word As String, Optional Case_Sensitive As Boolean) Application.Volatile If Len(Specific_Word) = 0 Then Word_Count = (Len(Complete_Text) - Len(Application.WorksheetFunction.Substitute(Complete_Text, " ", ""))) + 1 Else If Case_Sensitive Then Word_Count = (Len(Complete_Text) - Len(Application.WorksheetFunction.Substitute(Complete_Text, Specific_Word, ""))) / Len(Specific_Word) Else Word_Count = (Len(Complete_Text) - Len(Application.WorksheetFunction.Substitute(UCase(Complete_Text), UCase(Specific_Word), ""))) / Len(Specific_Word) End If End If End Function
Click here to download the Practice file-
Watch the step by step video tutorial: