Like this:

Last time I talked about using VB to count the frequency of each character that occurred in a string. Now we’re going to go back to the problem that I started with, and that is how to do the same trick in Excel (specifically Excel VBA).

None of the steps have changed, btw. We still have to check which individual letters show up in the individual string. We still need to count their frequency. What is going to change is the details to implement the pseudocode.

For starters, the VBA string is not enumerable. That means that we can’t use the for each ch_letter as char in strSearchString. . .Next trick we used in VB.NET. What we will use here is the mid() and len() function. The latter returns the number of characters in a string and the former returns a substring within the string of a length we set. This is perfect for picking off individual characters. So, the code looks something like this:

Public Sub CountLetters()
Dim strText As String

strText = LCase(ActiveCell.Text)

Dim strSingleString As String
‘this is our string containing unique individual characters within the strText
‘string.

strSingleString = “”
Dim i_inx As Integer

For i_inx = 1 To Len(strText)
Dim strCh As String
strCh = Mid(strText, i_inx, 1)
If InStr(1, strSingleString, strCh) = 0 Then ‘the character is not currently in the string
strSingleString = strSingleString & strCh
End If
Next i_inx

As you can see, we are using a for loop like in the VB.NET. It has a little more work in it (we need to use the mid function to pull off individual characters), but it still works along the same principals.

In this situation, where we used Contains() method of the String object in VB.NET, we use the Instr() function in VBA. The actual declaration of Instr() is:

positionofcharacter = Instr(positiontostart,stringtosearch,substringtosearchfor)

In this case, we are searching from the start of the string. If the character isn’t in the string, it returns 0. strSingleString is initially empty, so the beginning characters are going to go straight on in. But when it does encounter a duplicate Instr() will return the position of that character in strSingleString. At that point, we just skip the character and move on.

Once we’ve built up our string of individual characters that are in the word, we’re now going to count them. We will still use the Split() method described in my previous blog but with a couple of changes. First off Split() is a standalone function rather than a method of string. Second, since there is no array object in VBA we will use another method, Ubound(), in place of Count().

UBound() returns the index for the last item in the array. It is also zero-based. So with an array consisting of 0,3, and 5, UBound will return 2. It is one less than the number of elements in the array. Remember what we said about Count() and finding the number of occurences in a string? It was one greater than the number. Since UBound is also one less than the number of elements, we can use that to find the frequency. So the finishing code ends up like this

i_row = ActiveCell.Row
i_cell = ActiveCell.Column
Dim i_curRow As String
Dim i_curCol As String

For i_inx = 1 To Len(strSingleString)
ActiveSheet.Cells(i_row + i_inx, i_cell).Formula = Mid(strSingleString, i_inx, 1)
ActiveSheet.Cells(i_row + i_inx, i_cell + 1).Formula = UBound(Split(strText, Mid(strSingleString, i_inx, 1)))

Next i_inx

End Sub

End result compared to the VB program:

There is some clipping because of the datagridview, but the results are the same. The spreadsheet is located in the box widget as stringbreak.zip. To run it, make sure macros are enabled, type text into any cell, make sure the cell is selected, and run the CountLetters macro.

In these two blogs we’ve shown how to count the frequency of letters. In future blogs we will go over how we can use this technique to create a frequency counter, charts, and even a frequency counter of words.

Update: My boxnet widget isn’t quite working the way that I thought so here are direct links to the two projects from my 4shared account:

• stringbreak.xlsm
• CharacterCounter.zip

Advertisement