Creating Dictionaries Dynamically

[Dal123]

How does one go about creating dictionaries dynamically (from variables) in VBA?

I was hoping to create dictionaries dynamically and generate the name of the dictionary as a position of a loop through an array/ dictionary, but it seems that's not allowed in VBA as it displays "Constant expression required".

Please Login or Register  to view this content.

In an effort to schematics I was hoping to make these Dictionaries global and call upon separate functions/ subs as this is a tiny part of a big project but I think that assigning variables/dictionaries globally dynamically is pretty awkward in VBA. Seems VB.Script it's not as bad but it may be a bit too much to expect from VBA without creating another project of work?

[mjr veverka]

Is not that enough ?

Please Login or Register  to view this content.

[Dal123]

Thanks porucha vevrku, no it doesn't accept a variable as the object name. It must be explicitly declared.

Please Login or Register  to view this content.

[mjr veverka]

In vba there are no such variable declarations:

Please Login or Register  to view this content.

with reference to their declarations in the loop.

Something like that below also does not exist:

Please Login or Register  to view this content.

The variable can only be declared once. You can not initially declare a variable as a dynamic array of 'String' type, and a moment later as an object "Scripting.Dictionary". If you want to store different types of data in a variable, you declare the variable as 'Variant'. You need to read about it a bit in the vba contextual help.

[jindon]

Create main dictionary and add to it dynamically as needed.
example

Please Login or Register  to view this content.

[Dal123]

Thanks so much Jindon, I've been flicking through it and writing it all down as it compiles to try to figure it out. Hopefully someone out there may have some spare time to help me grasp it?

• You use late binding here; is it absolutely neccessary? I'm new to dictionaries and have studied early binding and this is even more to take in. I know we don't have the values of the variables until runtime but https://www.experts-exchange.com/art...ss-in-VBA.html and https://excelmacromastery.com/vba-di...s_Late_Binding http://www.snb-vba.eu/VBA_Dictionary_en.htmlsuggest that late binding is best avoided if possible. Obviously I'm not criticising here whatsoever as I have no idea what I'm doing.
• Dim i As Long, myDetails - I've never seen the ending not being closed off before. The arrays use the array keyword function so they must be of variant type, is it saying i is of long data type when used in the array myDetails and you could set it as a different data type when used somewhere else?
• The MainDic variable being used is really confusing me, sometimes it's referencing the dictionary and other times it's creating dictionaries depending on where it's being run. I see you're creating an object via http://www.snb-vba.eu/VBA_Dictionary_en.html#L_0 an object variable but I can't figure out if you're creating nested dictionaries inside other dictionaries or a single dictionary with keys and values.

I greatly thank you for your input and you've certainly provided a great solution via late binding, I'm searching to try to find if it's possible via early binding; though I didn't find much things on nested dictionaries in vba.

[jindon]

Does Late/Early binding matter to you?
Doesn't make any difference to me for the functionality.

The thing that you want to know is to generate Dictionary dynamically, so I posted one example that I have done many times in the forum.

If you don't understand the arrays in an array, Jugged Array, it is a different thing.
You could get variable from the cell as well, it is just an example and I thought you would be able to adjust it.

[Dal123]

Thanks Jindon, your suggestion and help is much appreciated.

Early Binding would be better; like I say it's only because this is my first dictionary and all the tutorials and training I've encountered so far has been to do with early binding.

Yes this shows me how one can create objects dynamically and it is greatly appreciated.

[Dal123]

Sorry does anyone out there know how to do this via Early Binding? Trying to learn late binding all over again will be too much to process on a first attempt and all articles recommend early binding and late binding is hardly ever recommended.

[jindon]

I don't recommend early binding, because it need to set the reference to Scripting.Runtime...

Please Login or Register  to view this content.

[Dal123]

Thanks Jindon, yes late binding would be a concern if this was being shared among many machines however this is just myself on my first dictionary trying to figure it out so the benefit of sticking with the same method and being able to attain intellisence far outweighs the need of cross-compatibility.

I think the problem is the code supplied doesn't actually create dictionaries dynamically (please correct me if I'm wrong). A single dictionary is created MainDic, then the code is just looping through and populating the dictionary with the values of the array.

[jindon]

I think the problem is the code supplied doesn't actually create dictionaries dynamically (please correct me if I'm wrong). A single dictionary is created MainDic, then the code is just looping through and populating the dictionary with the values of the array.

If you think so, I leave this to someone else.

[Dal123]

If you think so, I leave this to someone else.

My sincere apologies Jindon, I didn't (and still don't properly) understand dictionaries (and all of VBA ). I need to play around with dictionaries to try to figure it out before even trying to understand dynamic creation; it's just too much for me to grasp at the moment.

My apologies again.