Dictionaries and arrays explanation

[Xrull]

I'm trying to improve my understanding of Dictionaries and Arrays (I'm between jobs so I have the time) and I was looking at a piece of code in a response to a thread "generate array from multiple rows" sometime in 2017.

The answer was provided by Jindon.

https://www.excelforum.com/excel-pro...le-rows-2.html

Can anyone explain how the code below works?

Is there a good tutorial on how to understand how to use Dictionaries and Arrays?

Please Login or Register  to view this content.

Regards,
Xrull

[Xrull]

I'm unable to get the code tags to work; it could be a problem with my browser.

[jindon]

Before start, you need to wrap the vba code with the code tags.


Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html

[AliGW]

I have amended them for you. You had added both opening AND closing tags at either end of the code instead of the opening tag at the start and the closing tag at the end.

[jindon]

Xrull,

The purpose of the code was to fulfill the requirement in the original thread.

I will import this into SQL via csv and the script reads all fields equally so if the max is 4 data points and one of the rows only has 2 data points, I will need 4 comma delimiters anyway.

Please Login or Register  to view this content.

Do a step debug with LocalWindow open, so that you can see all the variables.
If you still have problem, just point out the line(s) that you don't understand.

[bakerman2]

Some more info on Dictionaries and Arrays.

http://www.snb-vba.eu/inhoud_en.html

[Xrull]

Jidon

This is the technique I don't get.

Please Login or Register  to view this content.

I've been trying to understand how these work together: This looks like one part .Item(a(i,1)) (2) and this is part two (CStr(a(i,3)))

I've put it in the watch window, but I get " Expression not defined in context " for the following:

Please Login or Register  to view this content.

but it doesn't work unless they are joined.

The locals window shows what all the variables are, but not how they work in harmony with each other.

Thanks again

BakerMan2

I looked at Snb's site on Sunday and got a better understanding of dictionaries; I worked my way through about 3/4 of the examples; probably it is there somewhere. I'll look at the rest of the examples today.

Thanks

[jindon]

OK, that part is a bit tricky.

Let me explain about Jagged Array, Arrays in an Array, first.

Take an example below

Please Login or Register  to view this content.

If you look at the Local Window and expand the variable MinArray by clicking + sign, it shows like

MainArray(1)(1)'<--- John
MainArray(1)(2)'<--- H
MainArray(2)(1)'<--- Paul
MainArray(2)(2)'<--- M
MainArray(3)(1)'<--- Geroge
MainArray(3)(2)'<--- H

This is how it is indexed.
So, if you want to refer to 1st element of 2nd elements in MainArray

Please Login or Register  to view this content.

will give you "Paul" and this is, so you can change it like

Please Login or Register  to view this content.

Get back to your question
When Dictionary stores an array in its item, only object type element can be updated in that way.

Please Login or Register  to view this content.

Let's add one variable already delcared, so that it would be easier for you.

Please Login or Register  to view this content.

This means w(1) doesn't change and w(2) will add new key when a(i,3) is new and .Item(a(i, 1)) is updated so.

Further question?

[Xrull]

Jindon,

Thanks for the explanation. Give me until weekend absorb and to mark it as solved.

Regards,
Xrull

[Xrull]

Based on Jindon's explanation, I understand the jagged array, however, I have a few questions.

1) How does using "=Empty" in the line below add unique items? I would have thought "Empty" would leave nothing. Probably Nothing and Empty are two different things or Empty means add.

2) How do I see what the two parts in the .Item() are in the lines below?

3) How does .Item know how to handle each argument or each piece of the array it precedes.

Please Login or Register  to view this content.

I tried emptying the two parts below:

Please Login or Register  to view this content.

and I got an error this line:

Please Login or Register  to view this content.

It seems it only works like this

Please Login or Register  to view this content.

The question is why and how do I find documentation on how .Item(s) operates?

Still researching -- I'm getting there (I think),

Xrull.

[jindon]

Hope this answers to your questions.

Please Login or Register  to view this content.

[Xrull]

Jindon,

Thanks for taking the time to explain the various methods on how one can use the dictionary. I'm have a better understanding of how it works. With a little practice I should be able expand the usage of the dictionary for more than creating unique lists.

If I have anymore questions I'll let know.

This old dog is learning new tricks.
Attachment 626897

Regards,
Xrull