Can someone help me understand the differences between dictionaries and arrays

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,352
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I am struggling to understand the differences between when you should use a dictionary as opposed to when you should use a dictionary. Can someone share some insight please?
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Care to rephrase Dave
Rich (BB code):
when you should use a dictionary as opposed to when you should use a dictionar
But have a read here.....
 
Upvote 0
I'm sorry, I meant to say when should I use a dictionary as opposed to an array.
 
Upvote 0
I feel as though arrays would be faster as even though you have to load the entire array and read through it, it is done in memory. I would imagine it may minimise interactions with the worksheet, which is what slows down spreadsheets.
 
Upvote 0
I feel as though arrays would be faster as even though you have to load the entire array and read through it, it is done in memory. I would imagine it may minimise interactions with the worksheet, which is what slows down spreadsheets.

If you doing what in excel terminology is a lookup then a dictionary is much faster.
In the post below igold ran a timer on his original array solution over 65k records and it was over 4mins when he tested Fluff's dictionary solution it was under a second.
Refer:
 
Upvote 0
Thanks Alex, I guess they have different purposes. Could you give me some examples on instances where you would use one as opposed to the other please?
 
Upvote 0
I haven't used them a lot but the uses come under the heading.
Create a list of unique records
Compare lists
Aggregate values

Paul Kelly usually uses them with a simple class object eg in this 9 min video
 
Upvote 0
I think you really have to study the uses and reasons for their use for yourself.

I don't use them myself....and I should, but some reading for tonight. As I have said in the past, if one doesn't understand a particular part of VBA, use something else.
I wouldn't have thought anything in your recent project would require the need for more speed..
 
Upvote 0
Dictionaries, like Collections, are linked key and item pairs. If you don't need that, you don't need a dictionary/collection.
 
Upvote 0

Forum statistics

Threads
1,212,947
Messages
6,110,837
Members
448,302
Latest member
sniffit1st

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top