Word count on Excel worksheet

[Sam577]

Hi,
Is it possible to do a word count on an entire Excel worksheet (or preferably the whole workbook) using VBA? I can't use an array formula to do this as it needs to be a generic calculation that adds up A1:IV65535, i.e. the entire worksheet with the formula on cell IV65536 -- Excel hangs when I put this range in as I think it's too large.
Many thanks,
Sam

 

[AnotherHiggins]

As you probably know, what your asking is easy in Word. That makes me think that you are using Excel when you should be using Word.

That having been said....

Determining what a word is might be difficult. In the following code, I have assumed that all words will have spaces between them. So if we add the total number of spaces in a cell, then add one, we come up with how many words are in that cell.

If you have double spaces between any words, or any extra spaces at the beginning or end of cells, that will throw off the count.

Sub WordCount()
For Each cell In ActiveSheet.UsedRange
If Not cell = "" Then
    CurrentCellCount = _
        Len(cell) - _
        Len(Application.WorksheetFunction.Substitute(cell, " ", "")) _
        + 1
TotalCount = TotalCount + CurrentCellCount
End If
Next cell
MsgBox "Word Count: " & TotalCount
End Sub

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[macropod]

Hi John,

You can allow for (ie exclude) leading and trailing spaces, plus repeated internal spaces from the word count by the simple expedient of changing "Len(cell)" to "Len(Trim(cell))"

Cheers

[MS MVP - Word]

 

[Rofeu]

Hi,

take care with adding the 1. You only want to do that if the cell actually contains something.

i.e.

Len(Application.WorksheetFunction.Substitute(cell, " ", "")) _
        + Abs(trim(cell.value) <> vbnullstring)

Cheers,

Roel

 

[AnotherHiggins]

>take care with adding the 1. You only want to do that if the cell actually contains something.

Roel, that's why it is in an IF statement. It will only add 1 if the cell isn't empty.

macropod, good call on the TRIM function.

Sam, do you have it working?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[PHV]

plus repeated internal spaces
I doubt that the VBA Trim function does that ...

 

[Sam577]

Thanks guys. Yes, it is working. The only possible enhancement would be if it could sum up the total of all the words in all the sheets in the workbook.
This is the code I used. Thanks again for all your help.
Sam

Sub WordCount()
For Each cell In ActiveSheet.UsedRange
If Not cell = "" Then
CurrentCellCount = _
Len(Trim(cell)) - _
Len(Application.WorksheetFunction.Substitute(cell, " ", "")) _
+ Abs(trim(cell.value) <> vbnullstring)
TotalCount = TotalCount + CurrentCellCount
End If
Next cell
MsgBox "Word Count: " & TotalCount
End Sub

 

[AnotherHiggins]

>The only possible enhancement would be if it could sum up the total of all the words in all the sheets in the workbook.

That's easy.

Just wrap your current code with this:

For Each Sheet In ActiveWorkbook.Sheets
For Each cell In [red]Sheet[/red].UsedRange
'Current Code
Next Sheet

[red]NOTE[/red] that you must change activesheet to sheet so that each sheet is looped through.

PS - Roel, I see what you're saying now....If a cell contained just a single space, I would count that as a word.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[macropod]

Hi PHV,

Correct, "Len(Trim(cell))" won't clear up repeated internal spaces, but "Len(Application.Trim(cell))" will.

Cheers

[MS MVP - Word]