Подсчитать количество букв в последнем слове

Sub bb()
Dim a$
a = "bolshaya sobaka kokker "
a = Trim$(a)
a = Mid$(a, InStrRev(a, " "))
MsgBox Len(a) - Len(Replace$(a, "k", ""))
End Sub

Sub nn()
st = "qwq jhjhkkjh oikoikkytytk"
For i = Len(st) To 1 Step -1
 If Mid(st, i, 1) = "k" Then kol = kol + 1
 If Mid(st, i, 1) = " " Then Exit For
Next
MsgBox "Количество букв k в последнем слове=" & kol
End Sub

Sub aa()
st = "qwq jhjhkkjh oikoikkytytk"
For i = Len(st) To 1 Step -1
If Mid(st, i, 1) = " " Then flag = flag + 1
If flag = 1 And Mid(st, i, 1) = "k" Then kol = kol + 1
If flag = 2 Then Exit For
Next
MsgBox "Количество букв k в предпоследнем слове=" & kol
End Sub

  Function Split(ByVal Text As String, Optional ByVal Delimiter As String = " ", _
      Optional ByVal Limit As Long = -1, Optional CompareMethod As _
      VbCompareMethod = vbBinaryCompare) As Variant
      Dim res() As String
      If Len(Text) = 0 Then Split = res(): Exit Function
      ReDim res(0 To 100) As String
      Dim resCount As Long
      Dim length As Long
      Dim startIndex As Long
      Dim endIndex As Long
      
      length = Len(Text)
      startIndex = 1
      
      Do While startIndex <= length And resCount <> Limit
          ' get the next delimiter
          endIndex = InStr(startIndex, Text, Delimiter, CompareMethod)
          If endIndex = 0 Then endIndex = length + 1
          
          ' make room in the array, if necessary
          If resCount > UBound(res) Then
              ReDim Preserve res(0 To resCount + 99) As String
          End If
          ' store the new element
          res(resCount) = Mid$(Text, startIndex, endIndex - startIndex)
          resCount = resCount + 1
          
          startIndex = endIndex + Len(Delimiter)
      Loop
      
      ' trim unused values
      ReDim Preserve res(0 To resCount - 1) As String
  
      ' return the array inside a Variant
      Split = res()
  
  End Function
 
Public Function InstrRev(ByVal StringCheck As String, _
    StringMatch As String, Optional Start As Long = -1, _
    Optional Compare As VbCompareMethod = vbBinaryCompare) _
    As Long
Dim lStartPoint As Long
Dim lEndPoint As Long
Dim lSearchLength As Long
Dim lCtr As Long
Dim sWkg As String
 
    InstrRev = 0
 
    If Len(StringMatch) > Len(StringCheck) Then Exit _
        Function
    If Start < -1 Or Start = 0 Or Start > Len(StringCheck) _
        Then Exit Function
 
    lSearchLength = Len(StringMatch)
    lStartPoint = IIf(Start = -1, Len(StringCheck), Start)
    lEndPoint = 1
 
    For lCtr = lStartPoint To lEndPoint Step -1
        sWkg = Mid(StringCheck, lCtr, lSearchLength)
        If StrComp(sWkg, StringMatch, Compare) = 0 Then
            InstrRev = lCtr
            Exit Function
        End If
    Next lCtr
End Function

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.EnableEvents = False
'<<<<<<<<<...>>>>>>>>>>>
'Ускорение: возврат пересчета значений ячеек и автообновления экрана
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True

Sub www()
 
Dim str$, m$, i&, cnt&
str = "Omne ignotum pro magnifico" 'будем считать буквы "i" в последнем слове
i = Len(str)
Do
    m = Mid(str, i, 1)
    If LCase(m) = "i" Then cnt = cnt + 1
    i = i - 1
Loop While m <> " " And i > 0
MsgBox cnt
 
End Sub