Solved

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

Posted on 2016-08-24
14
41 Views
Last Modified: 2016-10-03
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.
0
Comment
Question by:Alex Ont
  • 6
  • 5
14 Comments
 
LVL 3

Accepted Solution

by:
AL_XResearch earned 500 total points
ID: 41769503
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.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 41770265
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.
0
 
LVL 3

Expert Comment

by:AL_XResearch
ID: 41770333
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 ! :)
0
 
LVL 45

Expert Comment

by:aikimark
ID: 41770596
@AL_XResearch

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.
0
 
LVL 3

Expert Comment

by:AL_XResearch
ID: 41777960
Alex if you have the answer you need can you please close the question and award points ?
0
 

Author Comment

by:Alex Ont
ID: 41780796
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.
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 45

Expert Comment

by:aikimark
ID: 41781541
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.
0
 
LVL 3

Expert Comment

by:AL_XResearch
ID: 41781777
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
    
    Stop

End Sub

Open in new window


I hope this helps !
0
 
LVL 45

Expert Comment

by:aikimark
ID: 41782191
A dictionary item key must be a string, or equivalent, data type
0
 
LVL 3

Expert Comment

by:AL_XResearch
ID: 41782222
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.
https://www.experts-exchange.com/articles/3391/Using-the-Dictionary-Class-in-VBA.html

Try the code sample I sent - it does work trust me. I would not have posted un-tested code.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 41782253
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.
0
 
LVL 3

Expert Comment

by:AL_XResearch
ID: 41782264
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.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
My experience with Windows 10 over a one year period and suggestions for smooth operation
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now