I listened to Bill Jelen’s Mr. Excel podcast today (and, btw, it’s a great podcast) and he presented a problem of counting the occurrence of each character in a particular word. In this case he used antidisestablishmentarianism, which is a word that I believe everybody should work into their vocabulary at least once a day. The person wanted to know how many times a occured in that word, how many times n occured, and so on and so forth. Bill presented two solutions to this problem, but both had their problems. The first solution provided the correct count, but it repeated letters. The second (also with the correct count) didn’t repeat any letters, but it left some rather annoying blanks. So I decided to give this a shot.
You can pretty much break this problem up into two parts. Part one is getting all of the unique characters in a word. For instance, if I had the word madman the unique characters are “madn”. So what would be the steps:
- Look at each character in the string.
- Check if it’s the first time that we have encountered the character. If it is, we note that character. If not, we ignore it.
Iterating over the string is slightly different in VBA (Excel) than it is in VB.NET. VB.NET is more robust and therefore easier so we’ll go over that first. A string can be thought of as an array of characters (in fact in many languages that’s exactly how it is stored). Being an array, it can be iterated over with the For. . .Each construct.
For each chLetter as Char in strSeachString
. . .
Next
Before we get into this a little bit longer, however, we should set up our Visual Basic Program. Fire up your Visual Basic and created a Windows Form application called “CharacterCounter”. This will require the following elements.
| Name |
Type of Component |
| frmMain |
Windows.Form |
| txtWord |
TextBox |
| btnCountCharacters |
Button |
| dgvFrequencyCount (with two columns, character and frequency in that order) |
DataGridView (we’re not connecting it to any datasource, but I like the layout anyway) |
Set it all up the way you want. Here’s a suggestion:
I’m not all that into having the form exactly the way I want. That’s not the point of this little exercise. The big thing is to name the items exactly as I did so that the code works (or you can be intelligent enough to sub in your own).
Now, for this exercise we’re going to be working on the assumption that we want a case-insensitive search. In our search, “A” and “a” are the same thing. So, we’ll transfer this into a lowercase version (it really doesn’t matter if it’s upper or lower as long as it’s all the same case).
Now, while we are iterating through the string we need to a way to store off characters as we come across them for the first time. There are many places to store it but for simplicity’s sake we’ll use. . .another string. The code goes something like this:
Private Sub btnCountCharacters_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCountCharacters.Click
Dim strIndividualChars As String
strIndividualChars = “”
Dim strLowerCaseSearchString As String
strLowerCaseSearchString = txtWord.Text.ToLower()
dgvFrequencyCount.Rows.Clear()
For Each chLetter As Char In strLowerCaseSearchString
If Not strIndividualChars.Contains(chLetter) Then
strIndividualChars = strIndividualChars + chLetter
End If
Next
The string object in VB.NET is far more flexible than it is in VBA and contains a number of useful functions. One of which is (coincidentally) Contains(). Contains returns a boolean value indicating if the parameter, in this case chLetter, is in the string object from which we are calling the function, in this case strIndividualChars. If it isn’t, we concatenate that letter onto strIndividualChars. If it is, we move onto the next character.
We have the single letters in a string, strIndividualChars. In the case of antidisestablishmentarianism strIndividualChars contains “antidseblhmr”. Every unique character that is in the former with no duplicates. Now onto the next part, finding out the unique count of each character of strIndividualChars in the original string. Again, we’re going to use a for each loop, this time on strIndividualChars. But onto determining the count.
The String object has several interesting functions. One of which is called Split(parameter). Split takes the string and returns and array. An array of what, you may ask? Well, it’s an array of the string divided by the parameter. For instance, given the following code.
dim strSentence as String
strSentence = “The quick brown fox jumped over whatever”
dim strArray() as String = strSentence.Split(” “)
strArray() now contains {“The”,”quick”,”brown”,”fox”,”jumped”,”over”,”whatever”}. As an array object it has several properties, one of which is count. Count tells you how many elements are in the array (in this case seven). Quick Question: how many spaces were in the string? 6, which is one less than then count. That holds true for whatever character you use in any type of string. The count of an occurance of a substring in a string will always be one less than the number of elements in the array created by using split on that string with the substring as the parameter. Follow? Probably easier to show with code:
For Each chLetter As Char In strIndividualChars
Dim row() As String = {chLetter, CStr(strLowerCaseSearchString.Split(chLetter).Count – 1)}
dgvFrequencyCount.Rows.Add(row)
Next
row() is a string array, btw, that we will use to feed in the results to output. The output will end up being something like this
The project is in the box widget on this site so you’re free to look through the code. That was the simple part. Tomorrow we’ll go through the VBA code, which throws a few more curves to us.
