Why use arrays in VBA when there are collections?

Solution 1:

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)

Solution 2:

@CharlesWilliams 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 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.

Solution 3:

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.