That's Great. Well explained. Being an Excel VBA beginner I have a confusion. We are trying to load a excel range into memory so we can manipulations it faster. Dictionary is already a memory portion. why first we are transferring our range into an array before adding it to dictionary? secondly what would be fastest lookup Array or Dictionary or Collection?
Hi Imran - a dictionary will only store unique keys so you can't just put an entire dataset into the dictionary. You need to put it into an array then push only the unique items into the dictionary. You determine the breadth of those items that go into the dictionary. Secondly a dictionary is faster than an array and I am not sure what is faster the collection or the dictionary. Hope that helped.
Yes here is a an example using the debug print. Your task is to go away and make it happen in a range. Remember you learn more by finding the answer yourself than by being given it. Dim j As Integer j = 1 For Each k In .keys Debug.Print j & " " & k, .Item(k) j = 1 + j Next k All the very best.
Hello Marcus, First of all thank you for your nice videos ! As I am new to VBA, I sometimes find it hard to understand pieces of code, so I'd be unable to reproduce. Here I can't figure out the line " .Item(ar(i, 1)) = .Item(ar(i, 1)) ", like doesn't it mean "A = A". Could you please explain in more details ?
You have to understand before this line of code the dictionary is BLANK - you have to fill it with something. This line effectively adds the value of the Array into each line of the dictionary. In retrospect I could have made it simpler by writing: .Item(ar(i, 1)) = ar(i, 1) That is effectively the same thing. The first item in the dictionary is equal to the first item in the array. I hope that helps. Marcus
![ARGENTINA vs. CHILE [3-0] | RESUMEN | ELIMINATORIAS SUDAMERICANAS | FECHA 7](http://i.ytimg.com/vi/De3AWmXzhuA/mqdefault.jpg)
Thanks for sharing. Great explanations. It's powerful
You are most welcome - glad it helped.
That's Great. Well explained. Being an Excel VBA beginner I have a confusion. We are trying to load a excel range into memory so we can manipulations it faster. Dictionary is already a memory portion. why first we are transferring our range into an array before adding it to dictionary? secondly what would be fastest lookup Array or Dictionary or Collection?
Hi Imran - a dictionary will only store unique keys so you can't just put an entire dataset into the dictionary. You need to put it into an array then push only the unique items into the dictionary. You determine the breadth of those items that go into the dictionary. Secondly a dictionary is faster than an array and I am not sure what is faster the collection or the dictionary. Hope that helped.
What an amazing content we have here. Some high level stuff. Thank you sir!
What a lovely thing to say Gileno. I don't get much feedback but thanks very much for taking the time.
Marcus its really great, I just have one query is there any way to add count against unique items?
Yes here is a an example using the debug print. Your task is to go away and make it happen in a range. Remember you learn more by finding the answer yourself than by being given it.
Dim j As Integer
j = 1
For Each k In .keys
Debug.Print j & " " & k, .Item(k)
j = 1 + j
Next k
All the very best.
Hello Marcus, First of all thank you for your nice videos ! As I am new to VBA, I sometimes find it hard to understand pieces of code, so I'd be unable to reproduce. Here I can't figure out the line " .Item(ar(i, 1)) = .Item(ar(i, 1)) ", like doesn't it mean "A = A". Could you please explain in more details ?
You have to understand before this line of code the dictionary is BLANK - you have to fill it with something. This line effectively adds the value of the Array into each line of the dictionary. In retrospect I could have made it simpler by writing:
.Item(ar(i, 1)) = ar(i, 1)
That is effectively the same thing. The first item in the dictionary is equal to the first item in the array.
I hope that helps.
Marcus
@@thesmallman thank you Marcus, I come to understand a bit more with your explanations. I hope I could follow along. Nice day
@@pedrolino8679 no worries Pedro. Thanks for interacting with my page. All the best.