VBA – Counting Strings – Function

After 5 articles, concerning Java I decided that it is time to write one for the VBA – the language which many programmers do not consider such, and yet it is  powerful tool, if you are an Excel or Access Application’s builder.

The current article would simply show a function, counting the times, a substring is repeated in a string. The function takes “sInput” and “sElement” as its arguments and returns an integer of the times an element is repeated. If you want to check what the function does, simply write ?CountInString(“textt tt”,”tt”) in the Immediate window in VBA and you will receive 2  as an answer.

Here is the code:

Option Explicit
Public Function CountInString(sInput As String, sElement As String) As Integer

Dim iCounter            As Integer
Dim iResult             As Integer
Dim iElementLength      As Integer

   iElementLength = Len(sElement)
   For iCounter = 1 To Len(sInput)
      If Mid(sInput, iCounter, iElementLength) = sElement Then iResult = iResult + 1
   Next iCounter

   CountInString = iResult

End Function

So far so good!