C# like List in VBA

Question

I'd like to create a List<T> on VBA like you create on C#, there is any way I can do that? I looked for questions about it here on SO, but I could not find any.

Answer 1

Generics appeared in C# 2.0; in VB6/VBA the closest you get is a Collection. Lets you Add, Remove and Count, but you'll need to wrap it with your own class if you want more functionality, such as AddRange, Clear and Contains.

Collection takes any Variant (i.e. anything you throw at it), so you'll have to enforce the <T> by verifying the type of the item(s) being added. The TypeName() function would probably be useful for this.

---

I took the challenge :)

Updated see original code here

List.cls

Add a new class module to your VB6/VBA project. This will define the functionality of List<T> we're implementing. As [Santosh]'s answer shows we're a little bit restricted in our selection of what collection structure we're going to wrap. We could do with arrays, but collections being objects make a better candidate, since we want an enumerator to use our List in a For Each construct.

Type Safety

The thing with List<T> is that T says this list is a list of what type exactly, and the constraint implies once we determine the type of T, that list instance sticks to it. In VB6 we can use TypeName to get a string representing the name of the type we're dealing with, so my approach would be to make the list know the name of the type it's holding at the very moment the first item is added: what C# does declaratively in VB6 we can implement as a runtime thing. But this is VB6, so let's not go crazy about preserving type safety of numeric value types - I mean we can be smarter than VB6 here all we want, at the end of the day it's not C# code; the language isn't very stiff about it, so a compromise could be to only allow implicit type conversion on numeric types of a smaller size than that of the first item in the list.

Private Type tList
    Encapsulated As Collection
    ItemTypeName As String
End Type
Private this As tList
Option Explicit

Private Function IsReferenceType() As Boolean
    If this.Encapsulated.Count = 0 Then IsReferenceType = False: Exit Function
    IsReferenceType = IsObject(this.Encapsulated(1))
End Function

Public Property Get NewEnum() As IUnknown
    Attribute NewEnum.VB_Description = "Gets the enumerator from encapsulated collection."
    Attribute NewEnum.VB_UserMemId = -4
    Attribute NewEnum.VB_MemberFlags = "40"

    Set NewEnum = this.Encapsulated.[_NewEnum]
End Property

Private Sub Class_Initialize()
    Set this.Encapsulated = New Collection
End Sub

Private Sub Class_Terminate()
    Set this.Encapsulated = Nothing
End Sub

Verifying if the value is of the appropriate type can be the role of a function that can be made public for convenience, so a value can be tested to be valid by client code, before it's actually added. Every time we initialize a New List, this.ItemTypeName is an empty string for that instance; the rest of the time we're probably going to see the correct type, so let's not bother checking all possibilities (not C#, evaluation won't break at the first Or that follows a true statement):

Public Function IsTypeSafe(value As Variant) As Boolean

    Dim result As Boolean
    result = this.ItemTypeName = vbNullString Or this.ItemTypeName = TypeName(value)
    If result Then GoTo QuickExit

    result = result _
        Or this.ItemTypeName = "Integer" And StringMatchesAny(TypeName(value), "Byte") _
        Or this.ItemTypeName = "Long" And StringMatchesAny(TypeName(value), "Integer", "Byte") _
        Or this.ItemTypeName = "Single" And StringMatchesAny(TypeName(value), "Long", "Integer", "Byte") _
        Or this.ItemTypeName = "Double" And StringMatchesAny(TypeName(value), "Long", "Integer", "Byte", "Single") _
        Or this.ItemTypeName = "Currency" And StringMatchesAny(TypeName(value), "Long", "Integer", "Byte", "Single", "Double")

QuickExit:
    IsTypeSafe = result
End Function

Now that's a start.

So we have a Collection. That buys us Count, Add, Remove, and Item. Now the latter is interesting, because it's also the Collection's default property, and in C# it would be called an indexer property. In VB6 we set the Item.VB_UserMemId attribute to 0 and we get a default property:

Public Property Get Item(ByVal index As Long) As Variant
    Attribute Item.VB_Description = "Gets/sets the item at the specified index."
    Attribute Item.VB_UserMemId = 0

    If IsReferenceType Then
        Set Item = this.Encapsulated(index)
    Else
        Item = this.Encapsulated(index)
    End If
End Property

Procedure Attributes

In VBA the IDE does not provide any way of editing those, but you can edit the code in Notepad and import the edited .cls file into your VBA project. In VB6 you have a Tools menu to edit those:

Attribute NewEnum.VB_UserMemId = -4 tells VB to use this property to provide an enumerator - we're just passing it that of the encapsulated Collection, and it being a hidden property it begins with an underscore (don't try this at home!). Attribute NewEnum.VB_MemberFlags = "40" is supposed to make it a hidden property as well, but I haven't yet figured out why VB won't pick up on that one. So in order to call the getter for that hidden property, we need to surround it with [] square brackets, because an identifier can't legally start with an underscore in VB6/VBA.

One nice thing about the NewEnum.VB_Description attribute is that whatever description you enter there, shows up in the Object Browser (F2) as a description/mini-documentation for your code.

Item Accessors / "Setters"

The VB6/VBA Collection doesn't allow directly writing values into its items. We can assign references, but not values. We can implement a write-enabled List by providing setters for the Item property - because we don't know if our T will be a value or a reference/object, we'll provide both Let and Set accessors. Since Collection doesn't support this we're going to have to first remove the item at the specified index, and then insert the new value at that place.

Good news, RemoveAt and Insert are two methods we're going to have to implement anyway, and RemoveAt comes for free because its semantics are the same as those of the encapsulated Collection:

Public Sub RemoveAt(ByVal index As Long)
    this.Encapsulated.Remove index
End Sub

Public Sub RemoveRange(ByVal Index As Long, ByVal valuesCount As Long)
    Dim i As Long
    For i = Index To Index + valuesCount - 1
        RemoveAt Index
    Next
End Sub

My implementation of Insert feels like it could get much better, but it essentially reads as "grab everything after the specified index, make a copy; remove everything after the specified index; add the specified value, add back the rest of the items":

Public Sub Insert(ByVal index As Long, ByVal value As Variant)
    Dim i As Long, isObjRef As Boolean
    Dim tmp As New List

    If index > Count Then Err.Raise 9  'index out of range

    For i = index To Count
        tmp.Add Item(i)
    Next

    For i = index To Count
        RemoveAt index
    Next

    Add value
    Append tmp

End Sub

InsertRange can take a ParamArray so we can supply inline values:

Public Sub InsertRange(ByVal Index As Long, ParamArray values())
    Dim i As Long, isObjRef As Boolean
    Dim tmp As New List

    If Index > Count Then Err.Raise 9  'index out of range

    For i = Index To Count
        tmp.Add Item(i)
    Next

    For i = Index To Count
        RemoveAt Index
    Next

    For i = LBound(values) To UBound(values)
        Add values(i)
    Next
    Append tmp

End Sub

Reverse has nothing to do with sorting, so we can implement it right away:

Public Sub Reverse()
    Dim i As Long, tmp As New List

    Do Until Count = 0
        tmp.Add Item(Count)
        RemoveAt Count
    Loop

    Append tmp

End Sub

Here I thought, since VB6 doesn't support overloads. that it would be nice to have a method that can add all items from another list, so I called that Append:

Public Sub Append(ByRef values As List)
    Dim value As Variant, i As Long
    For i = 1 To values.Count
        Add values(i)
    Next
End Sub

Add is where our List becomes more than just an encapsulated Collection with a couple extra methods: if it's the first item being added to the list, we have a piece of logic to execute here - not that I don't care about how many items there are in the encapsulated collection, so if all items are removed from the list the type of T remains constrained:

Public Sub Add(ByVal value As Variant)
    If this.ItemTypeName = vbNullString Then this.ItemTypeName = TypeName(value)
    If Not IsTypeSafe(value) Then Err.Raise 13, ToString, "Type Mismatch. Expected: '" & this.ItemTypeName & "'; '" & TypeName(value) & "' was supplied." 'Type Mismatch
    this.Encapsulated.Add value
End Sub

The source of the error raised when Add fails is the result of a call to ToString, a method that returns... the name of the type, including the type of T - so we can make it a List<T> instead of a List(Of T):

Public Function ToString() As String
    ToString = TypeName(Me) & "<" & Coalesce(this.ItemTypeName, "Variant") & ">"
End Function

List<T> allows adding many items at once. At first I implemented AddRange with an array of values for a parameter, but then with usage it occurred to me that again, this isn't C#, and taking in a ParamArray is much, much more handy:

Public Sub AddRange(ParamArray values())
    Dim value As Variant, i As Long
    For i = LBound(values) To UBound(values)
        Add values(i)
    Next
End Sub

...And then we get to those Item setters:

Public Property Let Item(ByVal index As Long, ByVal value As Variant)
    RemoveAt index
    Insert index, value
End Property

Public Property Set Item(ByVal index As Long, ByVal value As Variant)
    RemoveAt index
    Insert index, value
End Property

Removing an item by providing a value instead of an index, would require another method that gives us the index of that value, and because we're not only supporting value types but also reference types, this is going to be very fun, because now we need a way to determine equality between reference types - we can get reference equality by comparing ObjPtr(value), but we're going to need more than just that - the .net framework taught me about IComparable and IEquatable. Let's just cram these two interfaces into one and call it IComparable - yes, you can write and implement interfaces in VB6/VBA.

IComparable.cls

Add a new class module and call it IComparable - if you really plan to use them for something else then you could put them in two separate class modules and call the other one IEquatable, but that would make you two interfaces to implement instead of one, for all reference types you want to be able to work with.

This isn't mock-up code, all that's needed is the method signatures:

Option Explicit

Public Function CompareTo(other As Variant) As Integer
'Compares this instance with another; returns one of the following values:
'   -1 if [other] is smaller than this instance.
'    1 if [other] is greater than this instance.
'    0 otherwise.
End Function

Public Function Equals(other As Variant) As Boolean
'Compares this instance with another; returns true if the two instances are equal.
End Function

List.cls

Putting the IComparable interface to use

Given that we have packed our IComparable with CompareTo and Equals, we can now find the index of any value in our list; we can also determine if the list contains any specified value:

Public Function IndexOf(value As Variant) As Long
    Dim i As Long, isRef As Boolean, comparable As IComparable
    isRef = IsReferenceType
    For i = 1 To this.Encapsulated.Count
        If isRef Then
            If TypeOf this.Encapsulated(i) Is IComparable And TypeOf value Is IComparable Then
                Set comparable = this.Encapsulated(i)
                If comparable.Equals(value) Then
                    IndexOf = i
                    Exit Function
                End If
            Else
                'reference type isn't comparable: use reference equality
                If ObjPtr(this.Encapsulated(i)) = ObjPtr(value) Then
                    IndexOf = i
                    Exit Function
                End If
            End If
        Else
            If this.Encapsulated(i) = value Then
                IndexOf = i
                Exit Function
            End If
        End If
    Next
    IndexOf = -1
End Function

Public Function Contains(value As Variant) As Boolean
    Dim v As Variant, isRef As Boolean, comparable As IComparable
    isRef = IsReferenceType
    For Each v In this.Encapsulated
        If isRef Then
            If TypeOf v Is IComparable And TypeOf value Is IComparable Then
                Set comparable = v
                If comparable.Equals(value) Then Contains = True: Exit Function
            Else
                'reference type isn't comparable: use reference equality
                If ObjPtr(v) = ObjPtr(value) Then Contains = True: Exit Function
            End If
        Else
            If v = value Then Contains = True: Exit Function
        End If
    Next
End Function

The CompareTo method comes into play when we start asking what the Min and Max values might be:

Public Function Min() As Variant
    Dim i As Long, isRef As Boolean
    Dim smallest As Variant, isSmaller As Boolean, comparable As IComparable

    isRef = IsReferenceType
    For i = 1 To Count

        If isRef And IsEmpty(smallest) Then
            Set smallest = Item(i)
        ElseIf IsEmpty(smallest) Then
            smallest = Item(i)
        End If

        If TypeOf Item(i) Is IComparable Then
            Set comparable = Item(i)
            isSmaller = comparable.CompareTo(smallest) < 0
        Else
            isSmaller = Item(i) < smallest
        End If

        If isSmaller Then
            If isRef Then
                Set smallest = Item(i)
            Else
                smallest = Item(i)
            End If
        End If
    Next

    If isRef Then
        Set Min = smallest
    Else
        Min = smallest
    End If

End Function

Public Function Max() As Variant
    Dim i As Long, isRef As Boolean
    Dim largest As Variant, isLarger As Boolean, comparable As IComparable

    isRef = IsReferenceType
    For i = 1 To Count

        If isRef And IsEmpty(largest) Then
            Set largest = Item(i)
        ElseIf IsEmpty(largest) Then
            largest = Item(i)
        End If

        If TypeOf Item(i) Is IComparable Then
            Set comparable = Item(i)
            isLarger = comparable.CompareTo(largest) > 0
        Else
            isLarger = Item(i) > largest
        End If

        If isLarger Then
            If isRef Then
                Set largest = Item(i)
            Else
                largest = Item(i)
            End If
        End If
    Next

    If isRef Then
        Set Max = largest
    Else
        Max = largest
    End If

End Function

These two functions allow a very readable sorting - because of what's going on here (adding & removing items), we're going to have to fail fast:

Public Sub Sort()
    If Not IsNumeric(First) And Not this.ItemTypeName = "String" And Not TypeOf First Is IComparer Then Err.Raise 5, ToString, "Invalid operation: Sort() requires a list of numeric or string values, or a list of objects implementing the IComparer interface."
    Dim i As Long, value As Variant, tmp As New List, minValue As Variant, isRef As Boolean

    isRef = IsReferenceType
    Do Until Count = 0

        If isRef Then
            Set minValue = Min
        Else
            minValue = Min
        End If

        tmp.Add minValue
        RemoveAt IndexOf(minValue)
    Loop

    Append tmp

End Sub

Public Sub SortDescending()
    If Not IsNumeric(First) And Not this.ItemTypeName = "String" And Not TypeOf First Is IComparer Then Err.Raise 5, ToString, "Invalid operation: SortDescending() requires a list of numeric or string values, or a list of objects implementing the IComparer interface."
    Dim i As Long, value As Variant, tmp As New List, maxValue As Variant, isRef As Boolean

    isRef = IsReferenceType
    Do Until Count = 0

        If isRef Then
            Set maxValue = Max
        Else
            maxValue = Max
        End If

        tmp.Add maxValue
        RemoveAt IndexOf(maxValue)
    Loop

    Append tmp

End Sub

The final touch

The rest is just trivial stuff:

Public Sub Remove(value As Variant)
    Dim index As Long
    index = IndexOf(value)
    If index <> -1 Then this.Encapsulated.Remove index
End Sub

Public Property Get Count() As Long
    Count = this.Encapsulated.Count
End Property

Public Sub Clear()
    Do Until Count = 0
        this.Encapsulated.Remove 1
    Loop
End Sub

Public Function First() As Variant
    If Count = 0 Then Exit Function
    If IsObject(Item(1)) Then
        Set First = Item(1)
    Else
        First = Item(1)
    End If
End Function

Public Function Last() As Variant
    If Count = 0 Then Exit Function
    If IsObject(Item(Count)) Then
        Set Last = Item(Count)
    Else
        Last = Item(Count)
    End If
End Function

One fun thing about List<T> is that it can be copied into an array just by calling ToArray() on it - we can do exactly that:

Public Function ToArray() As Variant()

    Dim result() As Variant
    ReDim result(1 To Count)

    Dim i As Long
    If Count = 0 Then Exit Function

    If IsReferenceType Then
        For i = 1 To Count
            Set result(i) = this.Encapsulated(i)
        Next
    Else
        For i = 1 To Count
            result(i) = this.Encapsulated(i)
        Next
    End If

    ToArray = result
End Function

That's all!

---

I'm using a few helper functions, here they are - they probably belong in some StringHelpers code module:

Public Function StringMatchesAny(ByVal string_source As String, find_strings() As Variant) As Boolean

    Dim find As String, i As Integer, found As Boolean

    For i = LBound(find_strings) To UBound(find_strings)

        find = CStr(find_strings(i))
        found = (string_source = find)

        If found Then Exit For
    Next

    StringMatchesAny = found

End Function

Public Function Coalesce(ByVal value As Variant, Optional ByVal value_when_null As Variant = 0) As Variant

    Dim return_value As Variant
    On Error Resume Next 'supress error handling

    If IsNull(value) Or (TypeName(value) = "String" And value = vbNullString) Then
        return_value = value_when_null
    Else
        return_value = value
    End If

    Err.Clear 'clear any errors that might have occurred
    On Error GoTo 0 'reinstate error handling

    Coalesce = return_value

End Function

---

MyClass.cls

This implementation requires, when T is a reference type / object, that the class implements the IComparable interface in order to be sortable and for finding the index of a value. Here's how it's done - say you have a class called MyClass with a numeric or String property called SomeProperty:

Implements IComparable
Option Explicit

Private Function IComparable_CompareTo(other As Variant) As Integer
    Dim comparable As MyClass
    If Not TypeOf other Is MyClass Then Err.Raise 5

    Set comparable = other
    If comparable Is Nothing Then IComparable_CompareTo = 1: Exit Function

    If Me.SomeProperty < comparable.SomeProperty Then
        IComparable_CompareTo = -1
    ElseIf Me.SomeProperty > comparable.SomeProperty Then
        IComparable_CompareTo = 1
    End If

End Function

Private Function IComparable_Equals(other As Variant) As Boolean
    Dim comparable As MyClass
    If Not TypeOf other Is MyClass Then Err.Raise 5

    Set comparable = other
    IComparable_Equals = comparable.SomeProperty = Me.SomeProperty

End Function

The List can be used like this:

Dim myList As New List
myList.AddRange 1, 12, 123, 1234, 12345 ', 123456 would blow up because it's a Long
myList.SortDescending

Dim value As Variant
For Each value In myList
   Debug.Print Value
Next

Debug.Print myList.IndexOf(123) 'prints 3
Debug.Print myList.ToString & ".IsTypeSafe(""abc""): " & myList.IsTypeSafe("abc")
    ' prints List<Integer>.IsTypeSafe("abc"): false

Answer 2

I know this is an old post, but I'd like to mention the following in addition to what's been discussed...

Array Lists

You can use an ArrayList, which is a weakly typed (uses objects, not strongly typed) linked list available in VBA. Here's some sample code demonstrating basic usage.

Sub ArrayListDemo()
    Dim MyArray(1 To 7) As String
    MyArray(1) = "A"
    MyArray(2) = "B"
    MyArray(3) = "B"
    MyArray(4) = "i"
    MyArray(5) = "x"
    MyArray(6) = "B"
    MyArray(7) = "C"
    Set L1 = ToList(MyArray)
    L1.Insert L1.LastIndexOf("B"), "Zz"
    Set L2 = L1.Clone
    L2.Sort
    L2.Reverse
    L2.Insert 0, "----------------"
    L2.Insert 0, "Sort and Reverse"
    L2.Insert 0, "----------------"
    L1.AddRange L2.Clone
    Set L3 = SnipArray(L1, 9, 3)
    Debug.Print "---- L1 Values ----"
    For Each obj In L1
        Debug.Print obj & " (L1 & L3 = " & L3.Contains(obj) & ")"
    Next
    Debug.Print "---- L3 Values ----"
    For Each obj In L3
        Debug.Print obj
    Next
End Sub
Function ToList(ByVal Arr As Variant) As Object
    Set ToList = CreateObject("System.Collections.ArrayList")
    For Each Elm In Arr
      ToList.Add Elm
    Next Elm
End Function
Function SnipArray(ByVal ArrayList As Object, lower As Integer, length As Integer) As Object
    Set SnipArray = ArrayList.Clone
    lower = lower - 1
    upper = lower + length
    If upper < ArrayList.Count Then
        SnipArray.RemoveRange upper, (ArrayList.Count - upper)
    End If
    If lower > 0 Then
        SnipArray.RemoveRange 0, lower
    End If
End Function

Dictionary

Also, glad to see dictionary was mentioned. Here are a couple notes about how to use a dictionary in VBA and use it like a list:

Sub DictionaryDemo()
    'If you have a reference to "Microsoft Scripting Runtime..."'
    Set D = New Dictionary
    'Else use this if you do not want to bother with adding a reference'
    Set D = CreateObject("Scripting.Dictionary")

    'You can structure a dictionary as a zero based array like this'
    D.Add D.Count, "A"
    Debug.Print D(0)

    Set D = Nothing
End Sub

Answer 3

List<T> are index based collection which allows to attach any datatype to collection object which is not possible in VBA.

Index based Collection for VBA

• Arrays - On MSDN, declaring and using. See also Wikibooks.

Key-Value pair Collection for VBA

• Collection - MSDN, Wikibooks
• Dictonary - MSDN, Wikibooks

Alternatively you may create a class library in C# and consume in VBA. Refer this link