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