Lashing out at VB6 collections

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

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…


  1. Thanks for your feedback.

    I was about to scream that I am talking about VBA, not VB.NET, but then I realized this article describes a Dictionary class from Microsoft Scripting Library, which works with VB6. This is good news, but how do I get that library and how do I ensure my users have it? The scenario I am talking about is “salesman with a laptop” type of user. According to the KB article you mention

    You can obtain the Microsoft Scripting library (SCRRUN.DLL) by installing one of the following packages:
    Windows Script Host
    Windows NT Option Pack
    IIS 3.0
    Scripting 3.1 upgrade.

    Out of these only Windows Script Host looks promising, but I am not sure if it gets installed on Windows XP by default and whether the current version still has the library. If it does, then I’m good. If it does not, this is too much to ask from the users. I am stuck with the collections 🙂


  2. Wikipedia claims that:

    “Windows Script Host is distributed and installed by default on Windows 98 and later versions of Windows. It is also installed if Internet Explorer 5 (or a later version) is installed. Beginning with Windows 2000, the Windows Script Host became available for use with user login scripts.”


Leave a Reply

Your email address will not be published. Required fields are marked *