Message-ID: <3831B39F.83C3A0B5@fiastl.net>
Date: Tue, 16 Nov 1999 13:43:05 -0600
From: "David J. Braden"<tmy@fiastl.net><jeqvh725sl0.fsf@osf1.gmu.edu> 
Reply-to: tmy@fiastl.net
Organization: TMY Research
X-Mailer: Mozilla 4.6 (Macintosh; I; PPC)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: counting words in area?
References:  <38319700.3671F490@norlec.com>
Content-type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: microsoft.public.excel.misc
NNTP-Posting-Host: usr1ppp32.fiastl.net 216.15.165.166
Path: cppssbbsa01.microsoft.com!cppssbbsa05
Lines: 39
Xref: cppssbbsa01.microsoft.com microsoft.public.excel.misc:64366

Well, here's a different approach that covers the cells; it accounts for the
possibility of a null cell,and for the possibility that there may have been more
than one space between words.

Regards
Dave Braden

Option Explicit

Public function countWords(rng As range) As long
    dim lngAns As long
    dim rngCell As range
    
    for each rngCell In rng
        lngAns = lngAns + Wordcount(rngCell)
    next
    countWords = lngAns
end function

Private function Wordcount(c As range) As Integer
    dim iAns As Integer, iPos As Integer
    dim var As Variant
    
    var = application.Worksheetfunction.Trim(CStr(c))
    if Vartype(var) <> vbNull then
        iPos = 1
        do While iPos > 0
            iPos = InStr(iPos + 1, var, " ")
            iAns = iAns + 1
        loop
    end if
    Wordcount = iAns
end function

Geoff Taylor wrote:
> 
> Greg
> Here is a partial answer.
> The following code counts the number of spaces in the activecell, then
> adds one to infer the number of words. I'm afraid I don't have the skill
> to make this cover a multiple cell selection. Hopefully someone can do
> better....
> 
> Sub Wordcount()
>     dim Spacecount
>     dim Cellcount
>     Spacecount = 0
>         for Cellcount = 1 to Len(activecell)
>             if Asc(Right(activecell.value, Cellcount)) = 32 then
>                 Spacecount = Spacecount + 1
>             end if
>         next Cellcount
>     next
>     msgbox Spacecount + 1
> end Sub
> 
> HTH
> Geoff