Excel VBA Can the Dictionary in a Dictionary idea be applied beyond 2 levels?

I had this question after viewing Using the Dictionary Class in VBA.

I am attempting to sort data into dictionaries based off of several requirements.  The difficulty I face is that the "Unique" key is really a culmination of several different columns.  The reason I want the dictionary structure is because it give me the ability to query based of the subsets of the keys. Example:  The total Unique key would be Product&Store#&Day&Time which I could use to find the # of Product I sold on Friday between 1 & 3 PM @ store 1, but using the dictionary inside of a dictionary etc.  I could quickly ask how many of Product A did I sell in total, how many of Product A do I sell on Fridays and so forth.  Ideally I would be able to put upwards of 5 dictionaries together.
When I attempt to use the format laid out in this article, I keep running into the problem where keys already exist or the key doesn't exist causing the code to stop.

Thank you.  I can not attach an article due to the sensitive nature of the data.
Alex OntAsked:
Who is Participating?
AL_XResearchConnect With a Mentor Commented:
Short answer: Yes !

The 'keys' can be of any data type and the 'items' can be of any data type. I have done this myself.

Have the key as a concatenated string of each column and the 'item' for that key a dictionary.

In fact I have often taken it one step further and had the 'item' as am instance of a user defined type which contains multiple dictionaries and other variables. Another technique I use is to have the 'item' as an array of strings or dictionaries.

A little tip: when you are checking the contents of a dictionary the 'key' property will require the actual key value where as the 'keys' (plural) provides a zero-based array of keys. By the same token there are also 'item' and 'items' properties.
You can go another route and use a single dictionary with complex keys.  For every level, you append the current key value to its parent key value, with some delimiter character that doesn't exist in the strings.  I usually use (_ or / or | or ^) characters.

This works for both dictionaries and tree nodes.

When you want to get to individual items, use the entire key.  When you want to break up the key, use the SPLIT() function.
aikimark: exactly as I said - concatinate the columns with a delimeter. As long as its unique it doesn't matter to VBA what type or structure the key is - only matters to the coder ! :)
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.


I stopped reading your comment after "Yes".  I assumed you were describing a multi-deep level of dictionary storage.  Instead, you described the same concatenated key scheme that I described in my comment.  I would have posted a code snippet for the multi-dictionary layering had I read your comment.
Alex if you have the answer you need can you please close the question and award points ?
Alex OntAuthor Commented:
Apologies for the delay.  Concatenating the key ended up working, but I guess I was hoping that I wouldn't have to do that as It proved less elegant to spit all the data back out at the end.  Also I broke it up into essentially two different routine's in which I used the first routine to clean up the data to a certain point and then I used the second routine to finish it off.
You posed the question in such a way that it limited the answers we could give.  If you had posted some sample data and asked a more general question, maybe we could have helped in different ways.
Alex: As I said in an earlier comment
The 'keys' can be of any data type and the 'items' can be of any data type
that is not 100% true (there are some limitations) but you can make a custom class with as many strings and variables of any type as their are columns you need and then use an object of that class as the key.

This means that when you come to check the key all you need to do is set or test the properties of the type instance that makes up the key. For example - a basic class (I have only defined 'dtmKeyTime' object property properly due to time constraints - I have cheated and made the other class var public):
Public strStore As String
Public strProduct As String
Private dtmTime As Date

Public Property Let dtmKeyTime(dtmValue As Date)
    dtmTime = dtmValue
End Property

Public Property Get dtmKeyTime() As Date

    dtmKeyTime = dtmTime

End Property

Open in new window

The actual code to use this object (with a 'Stop' at the end so they the code will pause so you can use the watch window / immediate pane to test the dictionary values):
Sub testKey()

    Dim coKey As New clsMyKey
    Dim dicAllItems As Dictionary
    Dim dicSubItems As Dictionary
    ' Init. var.
    Set dicSubItems = New Dictionary
    dicSubItems.CompareMode = TextCompare
    Set dicAllItems = New Dictionary
    dicAllItems.CompareMode = TextCompare
    ' Set key's column values
    With coKey
        .strProduct = "Onion"
        .strStore = "London"
        .dtmKeyTime = TimeSerial(12, 30, 0)
    End With
    ' Define sub items
    With dicSubItems
        .Add "Alpha", 45
        .Add "Beta", 12
    End With
    ' Assign subitems to a key of multiple column values
    dicAllItems.Add coKey, dicSubItems

End Sub

Open in new window

I hope this helps !
A dictionary item key must be a string, or equivalent, data type
aikimark: I am sorry but in fact a Dictionary key can contain any data type (although there are some restrictions on if the type  / class declaration is in a public module) but it can be a 'primitive datatype' (String, Integer, Date, Variant) or an object.

To quote the another EE article:
With a Dictionary, the key can take any data type; for string keys, by default a Dictionary is case sensitive, but by changing the CompareMode property it can be made case insensitive.  In a Collection, keys are always strings, and always case insensitive.

Try the code sample I sent - it does work trust me. I would not have posted un-tested code.
While it might be "any" data type, the actual data type is something cast as a string.

I first ran into this problem many years ago, when using both Collection and Dictionary objects.  I made the mistake of trying to use (long) integer values as Dictionary keys and got key collisions during an add operation.
Well the internal workings 'may' convert to a string but in all the years I have used it any data type works (leaving aside the scope restrictions). In fact if your do a '? type name(dicTest.keys(0)') it won't always return string. Sure I have used Long variables in the past.

In any case as far as Alex's question goes my advice will solve his problem.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.