VBA: Count number of occurrences of the most frequent character in a string

Question

title says it all... I want to find the most occurred char in a string and then count its occurences.

Example: IN: "aabbbbcccd" OUT: Most occurred: "b", 4 times.

I don't even need to know which char is, just the number of repeats. I want to do this using only Excel VBA.

I tried to look for some algorithm, I found some approaches like: Find the most occurrence of a character in string C#? but I don't know how to port to VBA, I am afraid that I can't :(

Thanks !

score 4 · Accepted Answer · edited Oct 05 '22 at 00:03

4

Here is one way to get the count of the most frequent character in a string.

Sub chCount()
    Dim str As String: str = "abldfeoslgsasasessasae"
    
    Dim Ruler As Variant
    Dim Counter As Long
    Dim lCount As Long
    Dim maxCount As Long
    
    lCount = Len(str)
    
    For ch = 1 To lCount
      Ruler = Split(str, Mid(str, ch, 1))
      Counter = UBound(Ruler)
      If maxCount < Counter Then
        maxCount = Counter
      End If
    Next ch
    
    msgbox(maxCount)

End Sub

edited Oct 05 '22 at 00:03

miken32

42,008
16
111
154

answered Nov 23 '13 at 16:52

Automate This

30,726
11
60
82

2

Upvote for sheer cleverness. Nice! – Steve Rindsberg Nov 23 '13 at 16:58
Thank you for the kind words! – Automate This Nov 23 '13 at 17:01
The ruler concept is very smart, I was working myself to find the answer, but I think your code is simply the neatest solution. I don't know why I got downvote, but, trust me, I am working on that algorithm like 2 hours without easy solution until you enlighted me... – Lando Calc_rissian Nov 23 '13 at 17:46

score 3 · Answer 2 · answered Nov 23 '13 at 19:24

3

I know you want VBA but I can't resist a formula solution for anybody who might be interested or might want to convert to VBA.

With string in A1 use this "array formula" in another cell to get the count of the most frequent character in that string

=LEN(A1)-MIN(LEN(SUBSTITUTE(A1,MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),"")))

confirmed with CTRL+SHIFT+ENTER

answered Nov 23 '13 at 19:24

barry houdini

45,615
8
63
81

+1 Great use of formulas as usual Barry! – Automate This Nov 23 '13 at 21:13

VBA: Count number of occurrences of the most frequent character in a string

2 Answers2