Avatar of sjgrey
sjgreyFlag for Australia 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.

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
    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

Open in new window

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(iPhrase))

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.
Microsoft OfficeMicrosoft Excel

Avatar of undefined
Last Comment

8/22/2022 - Mon

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

Great - thanks very much

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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck