How to add words to this function?

In this function, how would I use the lcount in a sentence such as:
There are 5 words in the sentence.

Function CountWords(rRange As Range) As Long
Dim rCell As Range, lCount As Long
Dim Sentence As String
    For Each rCell In rRange
        lCount = lCount + _
          Len(Trim(rCell)) - Len(Replace(Trim(rCell), " ", "")) + 1
    Next rCell
CountWords = lCount
End Function
LVL 1
Alex CampbellAsked:
Who is Participating?
 
Martin LissOlder than dirtCommented:
The following should display "There are 2 words in the sentence."
Sub Test()

Const ASENTENCE = "Hello World"
Msgbox "There are " & countwords(ASENTENCE) & " words in the sentence."
End Sub

Open in new window

0
 
Martin LissOlder than dirtCommented:
Msgbox "There are " & countwords(<blah>) & " words in the sentence."
0
 
Alex CampbellAuthor Commented:
Thanks, but how exactly should it go in the code?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Tom FarrrelCommented:
There is a variable Sentence that you do not use. You can use it to capture
Sentence = Trim(rCell)
and then use it in
lCount = lCount + Len(Sentence) - Len(Replace(Sentence, " ", "")) + 1

That makes your code easier to read and (not significantly) faster
0
 
Alex CampbellAuthor Commented:
What would the new code look like?

Function CountWords(rRange As Range) As Long
Dim rCell As Range, lCount As Long
Dim Sentence As String
    For Each rCell In rRange
        lCount = lCount + _
          Len(Trim(rCell)) - Len(Replace(Trim(rCell), " ", "")) + 1
    Next rCell
CountWords = lCount
End Function
0
 
Ryan ChongCommented:
i guess you meant to make the function returns as String instead of Long, like this?

Function CountWords(rRange As Range) As String
    Dim rCell As Range, lCount As Long
    Dim Sentence As String
    
    For Each rCell In rRange
        lCount = lCount + _
          Len(Trim(rCell)) - Len(Replace(Trim(rCell), " ", "")) + 1
    Next rCell
    CountWords = "There are " & lCount & " words in the sentence"
End Function

Open in new window

0
 
Alex CampbellAuthor Commented:
Thanks
0
 
Martin LissOlder than dirtCommented:
You’re welcome and I’m glad I was able to help.

If you expand the “Full Biography” section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange Most Valuable Expert (MVE) 2015, 2017
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2017
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.