WordInLineSearch.vba

Function CountOfTimesOneWordOccursInALine(WordToFind As String, Line As String, delimiter As String) _
As Integer
' Count the number of times a single word (aka a shorter string) occurs in a line
' (aka a longer string)

    'First do the easy thing - if the search string does not occur in the target string,
    'return count of 0
    If (InStr(Line, WordToFind) = 0) Then
        CountOfTimesOneWordOccursInALine = 0
        Exit Function
    End If
    
    Dim searchStringCount As Integer
    Dim uniqueSet As New Scripting.Dictionary
    Dim wordsInLine() As String
    
    'Got here? Found the search string at least once, so insert into the (unique key) dictionary...
    searchStringCount = 0
    uniqueSet.Add WordToFind, Null
    
    'get the target words into an array so we can walk them...
    wordsInLine = Split(Line, delimiter)
    
    Dim wordInLine As Variant
    For Each wordInLine In wordsInLine
        If uniqueSet.Exists(wordInLine) Then
            searchStringCount = searchStringCount + 1
        End If
    Next wordInLine
    
    CountOfTimesOneWordOccursInALine = searchStringCount
End Function