VBA code: Words Statistic

Question

Hello!

I want to create a word statistic. Someone on a polish forum help

me with this, but partially. In this moment "my" code output the following

results: words, frequency of occurence, and page number.

I also need another column with page numbers, where the page number looks like that:

1-3, 5, 6-8. Now the page numbers looks like this: 1,2,3,5,6,7,8. When the

page numbers are consecutive (sequential), beetwen a page numbers I need to

add a hyphen (dashes). Solution for this problem, I found at below page,

but for an awk language:

http://docstore.mik.ua/orelly/unix/s...12/combine.idx

and also:

http://docstore.mik.ua/orelly/unix3/sedawk/ch12_02.htm

In another column I also need a number of page numbers. For example, when a

some word appear on pages 1,3,5,7, in output results in another column will

be 4.

And the last case: my code found all words in document. I need also search

for the keywords, for example this code do it:

http://microsoft-personal-applicatio...3/Default.aspx

I have also a some problems with some lines of my code:

strSingleWord < "a" Or strSingleWord > "z" 'fist character of word must

be a letter, this line ignore a special word, like "3w" "4you"

"3ware" "3love" "2day" , and so on...

I have also problem with some words, like: "@SCHL" "m&m", and so

on..., something line of code ignore this words.

Anyone help me with this?

You can also visit my thread at polish forum:

http://www.coderscity.pl/ftopic35919.html

[P.S. sorry, how can I add my code?]

Below my code:

http://www.msofficeforums.com/word/5101-table-contents.html

Thanks for your attention!

Regards

Answer 1

Thanks Andreas and macropod for your replies.

I’m very beginner in VBA, Ms Word especially and I can’t to run your codes. I must combine your codes with mine, or what?

I need to know how use your codes step-by-step like here:

http://www.vbaexpress.com/kb/getarticle.php?kb\_id=341

1.Open an Excel workbook

2.Select Tools/Macro/Visual Basic Editor

3.In the VBE window

4.Copy and paste the code …

[quote]Why 4? What about page 2 and 6? I can't see this pages in your list[/quote]

[how quotes in forum?]

That’s only example.

Once more. I have a very long document, e.g., 50 pages. What I need to get in results (results generate in a new MS Word document, or in the end of active/current  MS Word document, or export results to MS Excel, because, then I must to sort them in Excel):

Results in columns:

1Column: Words (e.g. cat), 2C: Page numbers   (in which page the word, e.g. “cat” appear in document) with a hypen (when the page numbers are consecutive) or with a commas (when the page numbers are not consecutive) (e.g. 1-2,9,12-13,21-23,50) 3C: Page numbers without a hypen, only with commas,   (eg. 1,2,9,12,13,21,22,23,50) 4C: number of page numbers (Andreas, I explain, for e.g. in this case will be, let me count, 1,2,9,12,13,21,22,23,50 = 9, because the “cat” appear on ninth pages; you must to count the number of page numbers) 5C: Frequency of Occurrence (FQ) (e.g. “cat” appears 15 times in document, so we have 15)

Maybe once more, but shortly this time:

Results in columns:

I cat II 1-2,9,12-13,21-23,50 III 1,2,9,12,13,21,22,23,50 IV 9 V 15

And in the code I want to regulate: search for all words, or search for keywords, if it is possible, if not, maybe need two codes to do this.

I hope so it’s clearly.

Answer 2

Hello markos,

See reply to your post on the same topic at:

http://www.vbaexpress.com/forum/showthread.php?t=34749

---

Cheers,

macropod MS MVP - Word

Answer 3

I want to create a word statistic. Someone on a polish forum help

me with this, but partially. In this moment "my" code output the following

results: words, frequency of occurence, and page number.

Your code is very complicated, I suggest to use a dictionary instead an array for counting:

Sub Test()

  Dim D As Object

  Dim Item As Range

  Dim AllWords, AllCounts

  Set D = CreateObject("Scripting.Dictionary")

  D.CompareMode = vbTextCompare

  For Each Item In ActiveDocument.Words

    If Not D.Exists(Item.Text) Then

      D.Add Item.Text, 1

    Else

      D.Item(Item.Text) = D.Item(Item.Text) + 1

    End If

  Next

  MsgBox D.Count & " different words"

  'Get all words in one array

  AllWords = D.Keys

  AllCounts = D.Items

End Sub

I also need another column with page numbers, where the page number looks like that:

1-3, 5, 6-8. Now the page numbers looks like this: 1,2,3,5,6,7,8. When the

Sub Test()

  Dim S As String

  S = "1,2,3,5,8,9,10"

  Debug.Print Sequence(S, ",")

End Sub

Function Sequence(ByVal S As String, _

    Optional ByVal Delimiter As String = ",", _

    Optional ByVal Hyphen As String = "-") As String

  Dim Numbers() As String

  Dim I As Long, J As Long

  Dim AddHyphen As Boolean

  Numbers = Split(S, Delimiter)

  For I = 0 To UBound(Numbers) - 1

    If Numbers(I) + 1 = Numbers(I + 1) Then

      AddHyphen = True

    Else

      If AddHyphen Then

        Sequence = Sequence & Numbers(J) & Hyphen & Numbers(I) & Delimiter

        AddHyphen = False

      Else

        Sequence = Sequence & Numbers(I) & Delimiter

      End If

      J = I + 1

    End If

  Next

  If AddHyphen Then

    Sequence = Sequence & Numbers(J) & Hyphen & Numbers(I)

  Else

    Sequence = Sequence & Numbers(I)

  End If

End Function

In another column I also need a number of page numbers. For example, when a

some word appear on pages 1,3,5,7, in output results in another column will

be 4.

Why 4? What about page 2 and 6? I can't see this pages in your list.

And the last case: my code found all words in document. I need also search

for the keywords, for example this code do it:

Just ask the dictionary "if D.Exists(KeyWord) then" it gives you the answer if it is found or not.

Andreas.

Answer 4

Looks funny. :-))) I'll try again.

Answer 5

Am 28.10.2010 12:15, schrieb markos97: > I want to create a word statistic. Someone on a polish forum help > me with this, but partially. In this moment "my" code output the following > results: words, frequency of occurence, and page number. Your code is very complicated, I suggest to use a dictionary instead an array for counting: Sub Test() Dim D As Object Dim Item As Range Dim AllWords, AllCounts Set D = CreateObject("Scripting.Dictionary") D.CompareMode = vbTextCompare For Each Item In ActiveDocument.Words If Not D.Exists(Item.Text) Then D.Add Item.Text, 1 Else D.Item(Item.Text) = D.Item(Item.Text) + 1 End If Next MsgBox D.Count & " different words" 'Get all words in one array AllWords = D.Keys AllCounts = D.Items End Sub > I also need another column with page numbers, where the page number looks like that: > 1-3, 5, 6-8. Now the page numbers looks like this: 1,2,3,5,6,7,8. When the Sub Test() Dim S As String S = "1,2,3,5,8,9,10" Debug.Print Sequence(S, ",") End Sub Function Sequence(ByVal S As String, _ Optional ByVal Delimiter As String = ",", _ Optional ByVal Hyphen As String = "-") As String Dim Numbers() As String Dim I As Long, J As Long Dim AddHyphen As Boolean Numbers = Split(S, Delimiter) For I = 0 To UBound(Numbers) - 1 If Numbers(I) + 1 = Numbers(I + 1) Then AddHyphen = True Else If AddHyphen Then Sequence = Sequence & Numbers(J) & Hyphen & Numbers(I) & Delimiter AddHyphen = False Else Sequence = Sequence & Numbers(I) & Delimiter End If J = I + 1 End If Next If AddHyphen Then Sequence = Sequence & Numbers(J) & Hyphen & Numbers(I) Else Sequence = Sequence & Numbers(I) End If End Function > In another column I also need a number of page numbers. For example, when a > some word appear on pages 1,3,5,7, in output results in another column will > be 4. Why 4? What about page 2 and 6? I can't see this pages in your list. > And the last case: my code found all words in document. I need also search > for the keywords Just ask the dictionary "if D.Exists(KeyWord) then" it gives you the answer if it is found or not. Andreas.