top of page

VbaList

VbaList is a COM library accessible from VBA. It provides two 'List' collections, one for Strings and one for any type of Object, usable from VBA code in any VBA-enabled application (including Excel, Word, Outlook and PowerPoint) ... similar to a List<string> or List<object> in C#. 

As well as the obvious ability to Add, Get, Count, Insert, Clear and Remove items, some other capabilities are:

  • Enumerate the items in the List using 'For Each'

  • Sort or reverse the items in the List

  • Make the items held in the List unique

  • Get the List as text, as a bulleted list and as a numbered list

  • Add an array, remove an array, remove a range of items

  • Return the contents to a VBA array

  • Get the index or last index of a particular item, and check whether the List contains an item

  • Add, insert or remove one List to/from another List

Dependencies:

  • .NET Framework, any version from 4.6 to 4.8 (installed by default on all Windows 10 and 11 devices ... if using an older device, download from Microsoft here)

How to use VbaList:

  • Download, unzip, unblock the installer (the .exe file) then run the installer ... the installer must be run on each device the library is used on

  • For early binding, in your VBA Project, add a reference to 'VbaList', then ...

ListString

Dim oList as Object ' VbaList.ListString for early binding

Set oList = CreateObject("VbaList.ListString")

oList.Add "Zero"

oList.Add "One"

oList.Add "Two"

oList.Add "Three"

oList.Sort

Debug.Print oList.GetAsText

... displays:

One, Three, Two and Zero

ListObject

Dim oList As Object ' VbaList.ListObject for early binding
Dim vItem As Variant
Set oList = CreateObject("VbaList.ListObject")
oList.Add ActiveSheet.Range("J10")
oList.Add ActiveSheet.Range("D4")
oList.Add ActiveSheet.Range("A1")
oList.Add ActiveSheet.Range("G7")
oList.SortByProperty "Column", True
For Each vItem In oList
    Debug.Print vItem.Column
Next vItem

 

... displays:

 

1

4

7

10

Why use VbaList

  • Compared to an array: simpler to use, far wider range of properties and methods, do not need to manually manage the bounds

  • Compared to a Collection: simpler to use, far wider range of properties and methods ... and for ListString, faster (when early-bound) and provides type safety

  • Compared to an ArrayList: early binding available, faster, type safety for ListString, does not depend on outdated .NET Framework 3.5

Relative speeds based on a series of tests, compared to using a dynamic-bounds array:

  • Strings - ListString (early bound) 3.8 times longer; Collection 4.5 times longer; ListString (late bound) 35 times longer; Dictionary (early bound) 59 times longer; ArrayList 81 times longer; Dictionary (late bound) 182 times longer

  • Objects - Collection 0.9 times (faster); Dictionary (early bound) 1.2 times longer; ListObject (early bound) 1.2 times longer; Dictionary (late bound) 1.7 times longer; ListObject (late bound) 1.7 times longer; ArrayList 2.1 times longer

Documentation and examples:

  • VbaList is fully documented ... add a reference to 'VbaList' and then see the Object Browser

  • For examples of using ListString, download and unzip this Excel file

  • For examples of using ListObject, download and unzip this Excel file

Download VbaList:

  • To download VbaList, see the main downloads page

bottom of page