Hi Ron.

I found a problem with the code you provided. Sometimes I get a "Run-time
error '13': Type Mismatch". If I hit 'debug', it shows me the line:
w = Split(c.Value)

When I add a watch for c, this is what I see:
Value: Error 2029 Type: Range/Range

When I add a watch for c.value, this is what I see:
Value: Error 2029 Type: Variant/Error

I traced this to a cell which contains the following text:
#NAME?

However, after removing the data from that cell and trying again, I now get
"Run-time error '5020': Application-defined or object-defined error".

This error occurs at the following line in the 'CountWord' function:
Set mc = re.Execute(s)


I'm not having much fun with this code, as I keep running into error that I
don't fully understand.


- James.



"Ron Rosenfeld" wrote:

On Tue, 23 Jun 2009 13:56:01 -0700, J741
wrote:

Thanks Ron. That worked.

Glad to hear it. Thanks for the feedback.


Now, how can I refine this to ignore words that are smaller than 3 letters
in length? Words like 'and', 'the', not', etc.

I would do that work in the StripWord function. That's where we clean up and
can also easily test words. If a null string is returned to the calling
routine, it already ignores it.

So, for example, to eliminate words that are 3 or fewer characters in length:

======================
Private Function StripWord(s As String) As String
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
'allow only letters, digits, slashes and hyphens
re.Pattern = "[^-/A-Za-z0-9]"
StripWord = re.Replace(s, "")
' eliminate words with length of three or less
If Len(StripWord) <= 3 Then StripWord = ""
Set re = Nothing
End Function
=======================

Other modifications as to unacceptable words, would be simple to do here, also.
--ron