Why use arrays in VBA when there are collections?
arrays, excel, vba
Solution
Several reasons to use arrays instead of collections (or dictionaries):
- you can transfer easily array to range (and vice-versa) with `Range("A1:B12") = MyArray`
- collections can store only unique keys whereas arrays can store any value
- collections have to store a couple (key, value) whereas you can store whatever in an array
See Chip Pearson's article about arrays for a better understanding
A better question would rather be why people would use collections over dictionaries (ok, collections are standard VBA whereas you have to import dictionaries)
Problem
many people use extensively arrays in Excel/VBA to store a list of data. However, there is the collection object which in my view is MUCH MUCH more convenient (mainly: don't need to re/define length of the list). So, I am sincerely asking myself if I am missing something? Why do other people still use arrays to store a list of data? Is it simply a hangover of the past?