VBA for Excel 2007 example-count words


VBA code to count words

In this example, you will learn VBA code to create a simple program that can count the number of duplicate words in Excel sheet. In this program, you will need two VBA loops--one to traverse through the rows and another for comparison. VBA built-in functions used in this program are Left, Split, and StrComp. The Left function is used to get on character from the supplied range of cells to identify the column label. Split function is used to split the text in each row in into an array of words that its elements can be compared against the word to count by using the StrComp function.

To run this example VBA code, you will need one Form, two Labels, one ComboBox, one TextBox, and two CommandButtons.

VBA for Excel:VBA program to count duplicate words


VBA code for this example:



Option Explicit
 
Private Sub CommandButton1_Click()
    Dim c As Integer
    c = 0
     If ComboBox1.Value <> "" And TextBox1.Value <> "" Then 'Check range and word
         c = CountWord(ComboBox1.Value, TextBox1.Value)
         MsgBox "Found:" & c 'Show number of words matched
    Else
        MsgBox "Enter range of cells or word to count" 'Alert the user
    End If
    
End Sub
 
Private Sub CommandButton2_Click()
    Unload UserForm1 'close the form when Cancel button is clicked.
End Sub
 
Private Sub UserForm_Initialize()
    'Sample ranges
    ComboBox1.AddItem "A1:A100"
    ComboBox1.AddItem "B1:B100"
    ComboBox1.AddItem "C1:C100"
    ComboBox1.ListIndex = 0
    'Focus the control
    ComboBox1.SetFocus
   
End Sub
 
Function CountWord(ByVal rng As String, what As Variant)
    Dim i, ct As Integer
    Dim warr() As String
    Dim x As Variant
    ct = 0
    Range(rng).Select 'Select rangle
   
    For i = 1 To Selection.Count 'Loop through the rows
        warr = Split(Range(Left(rng, 1) & i), " ") 'Split the text
        For Each x In warr
      
            If StrComp(x, what, vbTextCompare) = 0 Then 'Make comparison
                ct = ct + 1
            End If
        Next
       
    Next
    CountWord = ct
 
End Function


Comments





This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.