Someone I could not refuse asked me to “take a look” at some Excel macros written in Visual Basic For Applications (VBA), which is a limited version of VB6 running inside Excel.

Well, of course I found a fair share of cut-and-paste and inefficient code looking for things it has already found again and again. This is not the point. Of course there is verbose syntax, pesky Set keyword, lack of across-the-board code verification, et cetera. Some of these are painful, but this is not the point.

I hate VB6 the most for the lack of proper data structures and algorithms.

In VBA you have a whopping of choice of two data structures: arrays and collections. Most people stick with arrays, which is sometimes painful, but collections seem to bring some relief. Collection is a hybrid of dynamic array and map (a.k.a dictionary). It may contain objects, with or without key, that can be looked up by key, by index or via For Each loop:

Dim c As New Collection
c.Add obj1, "green"
c.Add obj2, "blue"
Set x = c(1)
Set y = c("green")

For Each obj in c
...
Next

At the first glance, collections provide huge advantage over arrays: they are truly dynamic, and they can be used as maps to quickly retrieve data by key. However, the collection interface lacks some important features:

1. There is no error-free way to find whether the collection contains a particular key.
2. There is no way to enumerate the keys or retrieve the object key in a For-Each loop
3. It is not possible to explicitly reorder objects within the collection
4. Keys are always strings and comparison is always case-insensitive

This makes the collection quite a strange data structure. It is neither an array (no reorder), nor dictionary (no access to keys). Additionally, inability to fetch the keys makes it impossible to create an exact copy of an existing collection. It also makes it impossible to sort the collection: no reorder means one must create a new sorted collection, no keys means you lose key information in the process.

On top of all that, there is no built-in sorting method, neither for arrays nor for collections. This seems a little odd for a language used in an office application environment such as Excel.

The bottom line is, VBA with its limited choice of data structures and lack of sorting algorithms sometimes makes you feel like a fish out of water, desperately gasping for air. Please, oh please, no more Excel macros…