How to Count the Frequency of Text in Excel!
Have you ever wondered how to count the frequency, i.e the number of times a specific text appears, in a range in Excel? We have already seen how to use the different COUNTING functions in a previous tutorial. We have also seen how to use Worksheet functions in VBA code in a previous tutorial.
We are now going to see how to use the COUNTIF Function, in VBA in order to retrieve the frequency of specific text. We are also going to allow user input through the use of an input box.
So, let’s get started with a simple example to see how to use the COUNTIF Function in VBA, in order to count the frequency of text.
A grip in tennis is the way the player at hand holds the racquet, to hit shots during the match. The grips a tennis player uses or prefers influences their style of play dramatically. This is due to the fact that in tennis, grips play a role in the amount of spin and pace, the player at hand generates.
There are different types of forehand grips and generally speaking, the type of grip the player prefers is dependent on the player themselves. The tennis racquet grip section is comprised of 8 bevels and these are used to help classify the type of grip.
The Continental grip describes a grip where the player’s index knuckle and heel pad rest on bevel 2 of the racquet. The Eastern Forehand grip describes a grip where the player’s base knuckle of the index finger is on bevel 3, as is the heel pad. The Semi-Western Forehand grip describes a grip where the player’s base knuckle of the index finger is on bevel 4, as is the heel pad.
The Western Forehand grip describes a grip where the player’s base knuckle of the index finger is on bevel 5 of the racquet, as is the heel pad. The Double-Handed Forehand grip is a grip that is obtained by holding the racket in a regular continental grip, then placing the left hand above it holding the racquet in a left-handed Semi-Western Forehand grip. This means that the reference bevels of the two hands are directly opposite one another. Famous players such as Monica Seles used the double-handed forehand grip.
The Eastern Backhand grip is obtained when the base knuckle of the index finger and the heel of the hand are on bevel 1. The Semi-Western Backhand grip is obtained when the base knuckle of the index finger is right on bevel 8. The Double-Handed backhand grip is obtained by holding the hand in a continental grip, then placing the left hand above in a left-handed Semi-Western forehand grip, this means that the reference bevels of the two hands are exactly opposite each other. With the evolution of the sport – different grips were popular among pro-players, during different eras of tennis.
A hypothetical tennis coach is evaluating the grips of the different players, registered at the tennis clinics he runs. He wants to input a certain type of grip and see how many players at the clinic use that particular grip.
The source data is shown below:
1) First things first, we will create a named range in the Workbook, to encompass all the grips that have been added already for each player in the Preferred Grip column.
2) Select the cell range B6: B100, type Grips in the Name box, which is located at the left end of the formula bar and press Enter.
3) Now, go to Developer>Code>Visual Basic in order to access the Visual Basic Editor (VBE).
4) Go to Insert>UserForm.
5) Go to View>Properties Window in order to see the Properties Window.
6) Using the Properties Window, change the name of the UserForm to frmTextFrequency, the BackColor to light yellow, the caption to See the frequency of specified text and the height to 345 and the width to 322.5.
7) Insert a button on the form and using the Properties Window, change the name of the button to cmdClickToSeeInputBox, the BackStyle to 0 – fmBackStyleTransparent, the caption to Click to enter the grip type you want to check the frequency of, and the height to 60 and the width to 270.
8) Insert a text box below the button and using the Properties Window change the name of the textbox to txtResult, set the Visible property to false and the height to 25.5 and the width to 138.
9) Right-click the button and select View Code as shown.
10) Enter the following code for the button click event:
Dim criteriaOne As String
Dim rng As Range
Dim countOne As Integer
Dim resultOne As String
criteriaOne = InputBox(“Choose the grip, you want to see the frequency of”, “Frequency Count”)
Set rng = Worksheets(“CountingtheFrequencyofText”).Range(“Grips”)
countOne = Application.WorksheetFunction.CountIf(rng, criteriaOne)
resultOne = “mentioned ” & countOne & ” times”
txtResult.Value = resultOne
txtResult.Visible = True
We want to ultimately use the COUNTIF Function in VBA, in order to tell us the frequency of a certain text. The variables are declared first in the code. The criteria will be obtained from user input in an input box and the range has been set as the named range of the workbook, which we named earlier. The text box appears with the results after the user enters the input. The visibility of the textbox is set to true again since we previously set it to false. Usually code like this requires error handling (since we are getting input from the user) but in this case, the COUNTIF Function will simply deliver 0 results if the user enters a grip that doesn’t exist or spells the name of the grip wrong.
11) We will now insert another button on the form, and using the Properties Window change the name of the button to cmdCloseForm, the BackColor to red, the caption to Close the Form, the ForeColor to white (this changes the font color to white) and the height to 30 and the width to 90.
12) Right-click the newly added button, and choose View Code and enter the following code for the button click event:
This allows the user to close the form using this button.
13) Go back to the Worksheet, and go to Developer>Controls>Insert ActiveX Controls and choose a button and place the button on the sheet.
14) Change the name of the button to cmdForm and the caption to Check the Frequency of Text Form, while in Design mode.
15) Right-click the newly created button on the worksheet and choose View Code and enter the following code for the button click event:
16) Return to the worksheet and make sure design mode is unchecked and click on the button to show the form.
17) The textbox is not shown initially since its visibility was set to False, click the Click to enter the type of grip you want to see the frequency of, button and an input box should appear as shown below.
18) Type Continental Grip in order to see the number of times Continental Grip appears in the range and then click Ok.
19) The textbox appears stating the number of times Continental Grip is mentioned in the range.
20) Close the form, using the red button and don’t forget to save your workbook as a macro-enabled workbook.
And there you have it.
Download Working File
Using worksheet functions in VBA is extremely time-saving and useful, by using the COUNTIF Worksheet function in VBA and the input box – in this case, we managed to get the input needed from the user as the criteria and then could count the frequency of the text.
Please feel free to comment and tell us which Worksheet functions you use in your VBA code.