Sub TestingDictionaries()
Dim Phrases As Scripting.Dictionary ' Dictionary of Phrases
Dim WordsInPhrase As Scripting.Dictionary ' Dictionary of the non trivial words in each phrase
Dim vPhrases As Variant
Dim vWords As Variant
Dim iPhrase As Integer, iWord As Integer
Dim wsPhrases As Worksheet
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Phrases"
Set wsPhrases = Worksheets("Phrases") ' List of extracted words
Set Phrases = New Scripting.Dictionary
Phrases.CompareMode = vbTextCompare
Set WordsInPhrase = New Scripting.Dictionary
WordsInPhrase.CompareMode = vbTextCompare
' Load dummy contents
For iPhrase = 1 To 10
For iWord = 1 To 5
WordsInPhrase.Add iWord, "Word #" & iWord & " in phrase #" & iPhrase
Next iWord
' Dump WordsInPhrase back out to test that it loaded
For iWord = 1 To WordsInPhrase.Count
wsPhrases.Cells(15 + iPhrase + 1, iWord + 2).Value = WordsInPhrase.Item(iWord)
Next iWord
Phrases.Add "Dummy phrase #" & iPhrase, WordsInPhrase
WordsInPhrase.RemoveAll
Next iPhrase
' ALL OK TO HERE -------------------------------------------------------
vPhrases = Phrases.Keys
For iPhrase = 0 To UBound(vPhrases)
wsPhrases.Cells(iPhrase + 1, 1).Value = vPhrases(iPhrase)
Set WordsInPhrase = Phrases.Item(vPhrases(iPhrase))
For iWord = 1 To WordsInPhrase.Count
wsPhrases.Cells(iPhrase + 1, iWord + 2).Value = WordsInPhrase.Item(iWord)
Next iWord
Next iPhrase
End Sub
Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.
TRUSTED BY
ASKER
To maintain the open ended scale of the store, I used Redim Preserve to build the WordsInPhrase array for each new phrase and I think that will solve the problem.