Dictionary Object

**AdamDay** · 01-07-2008, 07:08 PM

Hi, I am looking for a way to make a list of words out of some straightforward prose using a vba algorithm. I have attached an example it's txt with the data in lines (for some reason I can't upload xls today?)

-I'm looking to create a huge list of the words in the poem as I have started to do manually further down (the plan was to put this on a separate sheet). I discussed a similar algorithm with Leith Ross on this forum a few months ago and something called a "dictionary scripting object" was mentioned. I'm sad to say, I haven't been able to find out what this is. Any help anyone can offer would be greatly appreciated.

Adam

**rylo** · 01-07-2008, 07:40 PM

Adam

Here's a couple of ways, one using a collection, and the other using a dictionary.

Run each of the codes, and view the output. They both use a space as a word separator. The dictionary will see the and The as 2 different words, while the collection treats them the same.

 
Sub aaa()
  Set nodupes = New Collection
  For i = 1 To 12
    arr = Split(Cells(i, 1), " ")
    For j = LBound(arr) To UBound(arr)
      On Error Resume Next
      nodupes.Add Item:=arr(j), key:=arr(j)
      On Error GoTo 0
    Next j
  
  Next i
  Range("G:I").ClearContents
  For i = 1 To nodupes.Count
    Cells(i, "G") = nodupes(i)
  Next i
  
  Range("G:G").Sort key1:=Range("G1"), order1:=xlAscending, header:=xlNo
End Sub

 
Sub bbb()
  Set dic = CreateObject("scripting.dictionary")
  
  For i = 1 To 12
    arr = Split(Cells(i, 1), " ")
    For j = LBound(arr) To UBound(arr)
      If Not dic.exists(arr(j)) Then
        dic.Add Item:=arr(j), key:=arr(j)
      End If
    Next j
  
  Next i
  
  For Each ce In dic.items
    Cells(Rows.Count, 9).End(xlUp).Offset(1, 0).Value = ce
  Next ce
  
  Range("I:I").Sort key1:=Range("I1"), order1:=xlAscending, header:=xlNo

End Sub

rylo

**shg** · 01-07-2008, 08:30 PM

Dictionary has a CompareMode property that allows case-insensitive compares.

**wali** · 01-09-2008, 05:22 AM

Hello AdamDay,
thank you for the nice thread. I needed such a macro as well. :-)

Hello SHG,
i want to collect vocabulary from texts as well. Well the macro you have written for Adamday is almost what i needed too. I will be very thankful if you could modify the macro to fullfill my needs as well. Thank you very much in advance! :-)

1) Every time the macro is run it should not delet the older wordlist in Column G.It would mean that if i run makro over a text on firstday it will make me a word list in column G. The next time if i want to run the macro over another text, the older wordlist in the column G should not be deleted. Only the newer words which dont already exist should be added to the column G. And there should be further no duplicates in column G.
2) Column G should not be ordered alphabatically. The order can remain the way the text is processed. And every time the new text is processed the words should be simply put to the next empty cells in column G.
3) Your macro now sperates some times words containing signs such as ":" or "," or ";" or ".". It put for example"hand:", "Jabberwock," and "wabe:". Is it possible to add a function to the macro that it ignores specific signs. For example if it finds a word "hand:" that its put in Column G only "hand" and not the singn ":".
4) To modify the macro in such a way that even bigger texts can be processed. And that it can handle the job even if there are thousands of words already in column G.

AdamDay,

Through the modified macro one could enhance an existing dictionary. I would for example put all the target words of my existing dictionary in column G. Then i will put a text and run the macro. The macro will then only put those words in column G which dont exist in my older list already. The new suggested words could also contain some words which are nonsence. I would even dont delet them from G but put a specific signe infron of them in another column. That way the next time the macro is run over another text it wont put the same unuseful word in column G cus it will be there already. And that would save one the time to correct the same mistake each time. And one could always fish out the usefull words as well cus they wont be marked as unuseful.

**shg** · 01-09-2008, 09:28 PM

Hello SHG,
i want to collect vocabulary from texts as well. Well the macro you have written for Adamday is almost what i needed too. ...

I think you're crediting me for rylo's post.

However, posting a question in someone else's thread violates a crdinal rule of this and most other forums. Please start your own thread, and provide a link to this one for context as necessary.

Thanks.

**wali** · 01-10-2008, 02:45 AM

oops!!
i hope that rylo and AdamDay eccept my apology. Though i had read the rules long before but had forgotten that important one.

2). Never post a question in the Thread of another member. You MUST ALWAYS start you own New Thread.

SHG
To avoid such an embaracing thing again, i will read the rules each time in future before i post.

soooooooooooooorry!! :-)

**AdamDay** · 01-27-2008, 09:36 AM

Indeed! This is a great program. It still has a bit of bother with some of my larger sets, though. Is there an easy way around this? The address below will show you a similar set from an old US census. It has about 2m words (although far less unique words than some of my other sets).

http://rosuda.org/GOLD/data/CPS.txt

Thanks again

**shg** · 01-27-2008, 09:39 PM

OK, I went back to doing strings in 32K batches. My laptop took 23min to process the 13.8MB string.

**AdamDay** · 01-29-2008, 07:13 PM

WOW! I am delighted! It works perfectly. This is so much more than I hoped to get back. Many thanks to you. This has doubtlessly saved me weeks of toil!

**shg** · 01-29-2008, 07:44 PM

Well, good.

An 'input from text file' interface would be a better solution probably -- it could read line-by-line rather than sucking the whole thing in from the clipboard. Oughta reduce memory consumption dramatically.

I'll post back if I add it.

Dictionary Object

LinkBack

Thread Tools

Display

Hybrid View

Dictionary Object

Apology

2m words

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions