Sub CountWords()
    Dim WordCount As Long
    Dim Rng As Range
    Dim S As String
    Dim N As Long
    For Each Rng In ActiveSheet.UsedRange.Cells
        S = Application.WorksheetFunction.Trim(Rng.Text)
        N = 0
        If S <> vbNullString Then
            N = Len(S) - Len(Replace(S, " ", "")) + 1
        End If
        WordCount = WordCount + N
    Next Rng
    MsgBox "Words In ActiveSheet Sheet: " & Format(WordCount,"#,##0")
End Sub
 

=IF(LEN(A1)=0,0,LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))+1)

This works by first using TRIM to convert multiple spaces to single spaces and remove any leading or trailing spaces. Then it subtracts from the Length of A1 the Length of  A1 after all spaces have been removed (SUBSTITUE).  The difference is the number of spaces in A1. Add 1 to that and you get the number of words. The same function in VBA is shown below. Note that it uses Excel's worksheet function TRIM, not VBA's Trim function. The two are not the same. Both functions remove leading and trailing spaces, but Excel's TRIM changes multiple spaces within the string to single spaces, while VBA's Trim function leaves multiple spaces within the string intact. For example,

Debug.Print Application.WorksheetFunction.Trim("   a   b   c   ")
prints
a b c
while
Debug.Print Trim("   a   b   c   ")
prints
a   b   c

Function WordsInCell(Cell As Range) As Variant
    If Cell.Cells.Count <> 1 Then
        '''''''''''''''''''''''''''''''''''
        ' return #VALUE if there is more
        ' than one cell referenced by Cell.
        '''''''''''''''''''''''''''''''''''
        WordsInCell = CVErr(xlErrValue)
        Exit Function
    End If
    If Len(Cell.Text) > 0 Then
        With Application.WorksheetFunction
            WordsInCell = Len(.Trim(Cell.Text)) - _
                Len(.Substitute(.Trim(Cell), " ", "")) + 1
        End With
    Else
        WordsInCell = 0
    End If
End Function