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?
Several reasons to use arrays instead of collections (or dictionaries):
Range("A1:B12") = MyArraySee 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...).
Collection items without key is faster than adding or removing array items, but in almost all cases the difference won't matter. I can't think of any good reason to use Collection either.
Codeghost's answer is correct: looping through all the values of an array is faster than iterating a Collection or dictionary: so much so, that I always use the Keys() or Items() method of a dictionary when I need to do that - both methods return a vector array.
A note: I use the Dictionary class far more than I use collections, the Exists() method is just too useful.
There are, or course, drawbacks to collections and dictionaries. One of them is that arrays can be 2- or even 3-Dimensional - a much better data structure for tabulated data. You can store arrays as members of a collection, but there's some downsides to that: one of them is that you might not be getting a reference to the item - unless you use arrItem = MyDictionary(strKey) you will almost certainly get a 'ByVal' copy of the array; that's bad if your data is dynamic, and subject to change by multiple processes. It's also slow: lots of allocation and deallocation.
Worst of all, I don't quite trust VBA to deallocate the memory if I have a collection or dictionary with arrays (or objects!) as members: not on out-of-scope, not by Set objCollection = Nothing, not even by objDictionary.RemoveAll - it's difficult to prove that the problem exists with the limited testing toolkit available in the VBE, but I've seen enough memory leaks in applications that used arrays in dictionaries to know that you need to be cautious. That being said, I never use an array without an Erase command somewhere.
Jmax' answer has explained the other big plus for arrays: you can populate an array in a single 'hit' to the worksheet, and write back your work in a single 'hit.
You can, of course, get the best of both worlds by constructing an Indexed Array class: a 2-dimensional array with associated collection or dictionary objects storing some kind of row identifier as the keys, and the row ordinals as the data items.
Collections that auto-resize are slower (theoretically speaking, different implementations will obviously have their own mileage). If you know you have a set number of entries and you only need to access them in a linear fashion then a traditional array is the correct approach.
In my experience, arrays are much faster only if you know the size of the dataset you are processing. I don't see much of a difference in performance if you are using collections over dictionaries (using the scripting library). To avoid external references, I tend to stick with collections and use an embedded class to hold the data. The class makes the key : value pair updatable (which isn't usually possible with collections). You will need to mimic some dictionary-like functions (i.e. "Exists"), but this is straightforward.
To aid this, I've create a clsDictionary class that does everything, and there are plenty of examples available online. Some even include additional useful functions like "Insert" and "Sort" to keep the the records in key or value order. In a test of 10,000 items, the search speed is roughly a third of that of a standard dictionary. Other functions, like inserting a key : value are marginally faster using a collection, but I don't really notice this difference in the real world.
Another significant advantage of collections is being able to handle complex classes as values. While this is possible with dictionaries, it can get ugly very quickly. On balance it works well for me, but my recordsets are all relatively small. If I need to accommodate larger recordsets, I use ADODB and create a database table.
I strongly recommend Paul Kelly's https://excelmacromastery.com/excel-vba-collections/ as a tutorial.
