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
Avatar of TommySzalapski
Flag of United States of America image

Blurred text
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of sjgrey
Flag of Australia image


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

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews


IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo