asked on

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

Martin Liss

Msgbox "There are " & countwords(<blah>) & " words in the sentence."

Alex Campbell

ASKER

Thanks, but how exactly should it go in the code?

ASKER CERTIFIED SOLUTION

Martin Liss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Tom Farrrel

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

Alex Campbell

ASKER

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

Ryan Chong

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

Alex Campbell

ASKER

Thanks

Martin Liss

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