How to Count number of Non-Number Words in Excel using VBA Function

Question

For Example, I'd like a String such as, "This is a Bunch of Words in a sequence of 13 possible 1 words from a Dictionary or BookZZ or Libgen.io 1876" to give me a result of 19 (because "13", "1876" and "1" are numbers and should not be counted).

I created Two Functions which I'm trying to use within this Function I'm asking about:

The first one is the following:

' NthWord prints out the Nth Word of a String of Text in an Excel Cell such 
' as A1 or B19.

Function NthWord(ActiveCell As String, N As Integer)

Dim X As String
X = ActiveCell

X = Trim(Mid(Replace(ActiveCell, " ", Application.WorksheetFunction.Rept(" 
", Len(ActiveCell))), (N - 1) * Len(ActiveCell) + 1, Len(ActiveCell)))

NthWord = X

' In the Excel SpreadSheet:
' Trim (Mid(Substitute(A1, " ", Rept(" ", Len(A1))), (N - 1) * Len(A1) 
' + 1, Len(A1)))

End Function 

The second one is the following:

'NumberOfWords returns the number of words in a String 

Function NumberOfWords(ActiveCell As String)

Dim X As String
X = ActiveCell
Dim i As Integer
i = 0

If Len(Trim(X)) = 0 Then
    i = 0
Else:
    i = Len(Trim(X)) - Len(Replace(X, " ", "")) + 1
End If

NumberOfWords = i


' In the Excel SpreadSheet
' IF(LEN(TRIM(A1))=0,0,LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))+1)


End Function

My Attempt at printing the NumberOfNonNumberWords

Function NumberOfNonNumberWords(ActiveCell As String)

Dim X As String
X = ActiveCell
Dim count As Integer
count = 0
Dim i As Integer

If NumberOfWords(X) > 0 Then
    For i = 1 To NumberOfWords(X)

        If Not (IsNumeric(NthWord(X, i).Value)) Then
            count = count + 1
        End If

    Next i
End If


NumberOfNonNumberWords = count

End Function

However, when I apply this function in the Excel Worksheet, I get an output of #VALUE! and I'm not sure why. How do I fix this?

Answer 1

Split the whole string then count non-numeric elements.

function abcWords(str as string) as long

    dim i as long, arr as variant

    arr = split(str, chr(32))

    for i=lbound(arr) to ubound(arr)
        abcWords = abcWords - int(not isnumeric(arr(i)))
    next i

end function