AddToArray VBA function

I'm going to discuss a function to add an element to a VBA Variant array: AddToArray.

In VBA, arrays have a fixed size. If you've already sized an array, while it's possible to resize it to add new items, doing so requires a couple lines of code with complicated indexing operations.

' Add another element to x.
ReDim Preserve x(UBound(x) + 1)
x(UBound(x)) = "d"

Most people might not think that this is terrible in terms of complexity, but it's a common enough operation that I think it would be good to add a Sub to our utility module that adds an element to an array in a generic way.

It's worth noting that resizing an array in this manner has very bad performance if you are working with large arrays, especially compared to alternate data structures that are widely available in other languages. ReDim Preserve will allocate an entirely new array with the new size, copy the old array to the new array, and finally discard the old array. Not only does this make resizing an array an O(n) operation, doing it several times generates a lot of garbage. Because of this, if there's a way to pre-identify the necessary size of an array, it's always preferable to size it to the correct length beforehand instead of adding to it one element at a time.

VBA does offer an efficient, growable collection type (aptly named Collection), but this class has several shortcomings. It has a strange API. It's 1-indexed. It's impossible to modify items that have been stored in a collection without removing and re-adding them. It only can store Variants; there's no type safety. And it's non-standard. Although this isn't so much of an issue if you're chiefly an Excel developer, there is no support for Collection objects in VBScript or VB6, and because of this, standard COM and ActiveX objects will only deal with arrays. Even APIs in Excel in general prefer to use arrays: for example, if you want to assign cell values to multiple cells in a range at once, you'll be doing so with an array.

Here is the AddToArray Sub. I'm not going to go into too much discussion of the details here, as I'm still in the middle of my series on ArraySearch.

Public Sub AddToArray(ByRef av As Variant, v As Variant)
    Dim array_size As Long
    Dim err_info As Variant
    
    If IsArray(av) Then
        ' Normally I hate using error handlers for an ordinary flow of
        ' execution, but there's no other option in VBA to test for an
        ' uninitialized, empty Array when it gets passed to a Sub as a
        ' Variant.
        ' If the parameter had been declared with "av() As Variant",
        ' "IsArray(av) And Not av" would detect uninitialized arrays.
        ' but then only variables explicitly declared as arrays would be
        ' passable without causing a compile-time error.
        
        On Error Resume Next
        array_size = UBound(av)
        err_info = Array(Err.Number, Err.Source, Err.Description)
        On Error GoTo 0
        
        If err_info(0) = 9 Then
            ReDim av(0)
            av(0) = v
        ElseIf err_info(0) <> 0 Then
            ' Re-raise other errors
            Err.Raise err_info(0), err_info(1), err_info(2)
        Else
            ReDim Preserve av(LBound(av) To array_size + 1)
            av(UBound(av)) = v
        End If
    ElseIf IsEmpty(av) Then
        av = Array(v)
    Else
        av = Array(av, v)
    End If
End Sub

And here are the tests for AddToArray.

#If False Then
' basTemp @UnitTests[start]
Private Sub TestAddToArray()
    ' add Long to empty non-array Variant
    Dim x As Variant
    AddToArray x, 1&
    AssertType x, "Variant()"
    AssertEqual Join(x, ","), "1"
    AssertType x(0), "Long"
    
    ' add String to singleton array
    AddToArray x, "Hello"
    AssertEqual Join(x, ","), "1,Hello"
    
    ' test adding problematic variant types to arrays
    AddToArray x, CVErr(1)
    AssertEqual x(2), CVErr(1)
    
    AddToArray x, Null
    AssertIsNull x(3)
    
    AddToArray x, Array(3, 6)
    AssertEqual UBound(x(4)), 1&
    AssertEqual x(4)(1), 6
    
    ' Adding to fixed-length arrays is not supported.
    Dim y(2) As String
    AssertErrStart
    AddToArray y, -1
    AssertError 10 ' This array is fixed or temporarily locked

    ' Test that adding to staticly typed arrays retains the type.
    Dim z() As String
    AddToArray z, "Hello"
    AssertType z, "String()"
    AssertType z(0), "String"
    
    ' Adding to a non-array value should convert the value to a
    ' Variant array containing the original value and new value.
    Dim a As Variant
    a = "Non-array"
    AddToArray a, 3
    AssertType a, "Variant()"
    AssertEqual UBound(a), 1&
    AssertEqual a(0), "Non-array"
    AssertEqual a(1), 3
    
    ' Multidimensional arrays
    Dim b() As Variant
    ReDim b(1, 1)
    
    AssertErrStart
    AddToArray b, 3
    AssertError 9 ' Subscript out of range
End Sub
' basTemp @UnitTests[end]
#End If