Can someone help me understand the differences between dictionaries and arrays

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,343
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

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,545
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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.....
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,343
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I'm sorry, I meant to say when should I use a dictionary as opposed to an array.
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,343
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,343
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

Thanks for the link Michael
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
1,147
Office Version
  1. 365
Platform
  1. Windows
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:
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,343
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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?
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
1,147
Office Version
  1. 365
Platform
  1. Windows
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
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,545
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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..
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,145
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Dictionaries, like Collections, are linked key and item pairs. If you don't need that, you don't need a dictionary/collection.
 

Forum statistics

Threads
1,136,770
Messages
5,677,630
Members
419,707
Latest member
Anna vib

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
Top