Our Blog

VBA Count string occurrences

I believe this is the smallest and fastest function you will find over there
It behaves like an inStr extension, of where it’s based on.

  '###############################################################################
  'Count the number of occurrences of one string inside another
  'Optional parameters:
  ' optCompare: 0=vbBinaryCompare (default) or 1=vbTextCompare
  ' start: Start position where search will begin, defaults to 1.
  'Version 1.0.0 100214 Marcelo Gennari
  '###############################################################################
  Public Function inStrCount(haystack As String, needle As String, _
    Optional optCompare As Integer = 0, Optional start As Long = 1) As Long
   Dim lastFound As Long
   Dim lenNeedle As Integer
   Dim rtn As Long
   If haystack = "" Then Exit Function
   lenNeedle = Len(needle)
   lastFound = InStr(start, haystack, needle, optCompare)
   Do While lastFound > 0
    rtn = rtn + 1
    lastFound = InStr(lastFound + lenNeedle, haystack, needle, optCompare)
   Loop
   inStrCount = rtn
  End Function


  Public Sub inStrCountTest()
   Debug.Print "Result x Expected = " & inStrCount("aca", "a") & " x 2"
   Debug.Print "Result x Expected = " & inStrCount("aaa", "a") & " x 3"
   Debug.Print "Result x Expected = " & inStrCount("aaccaaa", "aa") & " x 2"
   Debug.Print "Result x Expected = " & inStrCount("aaccaaaa", "aa") & " x 3"
   Debug.Print "Result x Expected = " & inStrCount("aaa", "aaaa") & " x 0"
  End Sub