sjgrey
asked on
Problem retrieving second level from dictionary of dictionaries (VBA in Excel 2007)
I'm trying to employ the dictionary of dictionaries techniques mentioned in a reply to a recent question about storing information where the number of items to be held is unpredictable. I'm having trouble retrieving the second level dictionary.
To diagnose what was going on I've set up the following code.
Down to the comment "ALL OK TO HERE" it behaves as expected.
The variant vPhrases retrieves the keys to the Phrases dictionary fine and the loop For iPhrase/Next iPhrase behaves as it should but the subsidiary dictionary WordsInPhrase is not retrieved by the line
The value of WordsInPhrase.Count is zero
I presume It's a simple error on my part but I'm having trouble tracking it down and any assistance will be most welcome.
To diagnose what was going on I've set up the following code.
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
Down to the comment "ALL OK TO HERE" it behaves as expected.
The variant vPhrases retrieves the keys to the Phrases dictionary fine and the loop For iPhrase/Next iPhrase behaves as it should but the subsidiary dictionary WordsInPhrase is not retrieved by the line
Set WordsInPhrase = Phrases.Item(vPhrases(iPhr ase))
The value of WordsInPhrase.Count is zero
I presume It's a simple error on my part but I'm having trouble tracking it down and any assistance will be most welcome.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.