excelvba count instances of substring code example

Example: excel vba count instances of substring

'VBA functions to count the occurrnces of a substring in a string.

'Choose your function flavor:

'Small and Slow:
Function InStrCount&(s1$, s2$, Optional Compare As VbCompareMethod = vbBinaryCompare)
    If Len(s1) Then If Len(s2) Then InStrCount = UBound(Split(s1, s2, , Compare))
End Function
    
        
'Super Fast (3 times faster than the above function):    
Function InStrCount&(s1$, s2$, Optional ByVal Start& = 1, Optional Compare As VbCompareMethod = vbBinaryCompare)
    Dim s2L&
    If Compare = vbBinaryCompare Then
        s2L = LenB(s2)
        If s2L Then
            Start = InStrB(Start, s1, s2)
            Do While Start
                InStrCount = InStrCount + 1
                Start = InStrB(Start + s2L, s1, s2)
            Loop
        End If
    Else
        InStrCount = InStrCount(LCase$(s1), LCase$(s2), Start)
    End If
End Function   
    
'------------------------------------------------------------------------------
    
MsgBox InStrCount("oAooBoCooDo", "oo")		'<--displays:  2