array push - Write a VBA function that accepts any type of array

Question

I'm trying to write a simple push function that can add an element to my VBA arrays.

I can't figure out how to allow it to accept typed arrays. So far, I can get the function to accept arrays explicitly typed as "Variant". See below

Function push(arr() As Variant, el As Variant) As Variant()
    ' function to redim an array and increment it by one.  If array is length 1 and element is empty, it will place the "el" param in the first index
    If IsEmpty(arr(UBound(arr))) And UBound(arr) = LBound(arr) Then
        arr(UBound(arr)) = el
    Else
        ReDim Preserve arr(LBound(arr) To UBound(arr) + 1)
        arr(UBound(arr)) = el
    End If
    push = arr
End Function

Sub testPush()
    Dim myArr() As Variant
    Dim newArr() As Variant
    myArr = Array("apple", "banana", "4")
    myArr = push(myArr, "coconut")
    Debug.Print Join(myArr, ", ")
    newArr = Array(1, 2, 3, 4)
    newArr = push(newArr, 7)
    Debug.Print Join(newArr, ", ")
End Sub

When I dimension myArr as string, e.g., Dim myArr() as String I see a compile error in my push function: Type mismatch: array or user defined type expected. Is there any way I can use a single function that attempts to increment an element to the end of an array, regardless of the array type?

Answer 1

Contrary to what I write below, maybe someone who knows more than me will tell you this is possible and exactly how you can do this.

---

I see that your function returns an array of variants:

Function push(arr() As Variant, el As Variant) As Variant()

which, if I understand correctly, can only be assigned to a variable of type Variant or Variant().

If I change the function to:

Function push(arr As Variant, el As Variant) As Variant

I think the function can now accept an array of any type¹ but it still returns a Variant -- which I believe can't be assigned to every typed array (meaning you'll still get a compiler error in some cases).

What might be an easier approach is to change the Function to a Sub and have the subroutine modify the array in-place. That way there is no assignment at the call site (since subroutines do not return values) and the code should compile. This also means any type error² will now occur at run-time.

Also, it's not clear to me what the point of the below is:

If IsEmpty(arr(UBound(arr)))

It seems like you're checking if the array's first element has been assigned something other than its default initialised value. But that check seems like it will only work for Variant types (which are initialised as empty). I think strings are initialised as "", numbers are initialised as 0, objects are initialised as Nothing, and so on. In short, I think the IsEmpty check may return false negative for types other than Variant. The aforementioned behaviour might be what you want, or it might not be (given that you say you want this Push code to work with arrays of any type).

Overall, one approach could be something like:

Option Explicit

Sub Push(ByRef someArray As Variant, ByVal someElement As Variant)
    ' This routine expects array to be 1-dimensional.
    ' and will modify the array in-place.
    ' The array must by dynamic (cannot Redim an array that
    ' was statically declared).

    Dim lastIndex As Long
    lastIndex = UBound(someArray)

    Dim arrayNeedsExtending As Boolean
    ' If you are using "IsEmpty" to work out if the first element
    ' has been assigned a value other than its default value at initialisation
    ' then you may need to see: https://stackoverflow.com/a/3331239/8811778
    ' as "IsEmpty" might only work for Variants and may return false
    ' negatives otherwise.
    arrayNeedsExtending = (lastIndex <> LBound(someArray)) Or Not IsEmpty(someArray(lastIndex))

    If arrayNeedsExtending Then
        ReDim Preserve someArray(LBound(someArray) To (lastIndex + 1))
    End If

    ' If you have an array of objects (hypothetically, instead of a collection), the line below
    ' will raise a syntax error since Set keyword is required for objects.
    someArray(UBound(someArray)) = someElement
End Sub

Private Sub TestPush()

    Dim someStrings() As String
    someStrings = Split("a,a", ",", -1, vbBinaryCompare)
    Push someStrings, "b"
    Debug.Assert (JoinArray(someStrings) = "a,a,b")

    Dim someBooleans() As Boolean ' Can only Push dynamic arrays
    ReDim someBooleans(0 To 1)
    Push someBooleans, True
    Debug.Assert (JoinArray(someBooleans) = "False,False,True")

    Dim someZeros() As Long
    ReDim someZeros(0 To 1)
    Push someZeros, 0
    Debug.Assert (JoinArray(someZeros) = "0,0,0")

End Sub

Private Function JoinArray(ByRef someArray As Variant, Optional delimiter As String = ",") As String
    ' Expects array to be 1-dimensional.
    ' Attempts to handle non-string types albeit without error handling.

    Dim toJoin() As String
    ReDim toJoin(LBound(someArray) To UBound(someArray))

    Dim arrayIndex As Long
    For arrayIndex = LBound(someArray) To UBound(someArray)
        toJoin(arrayIndex) = CStr(someArray(arrayIndex)) ' Will throw if type cannot be coerced into string.
    Next arrayIndex

    JoinArray = Join(toJoin, delimiter)
End Function

---

¹ Maybe not user-defined types though.

² Say you pass the string"a" to be pushed into a Long-typed array -- or any value that cannot be type-coerced into the array's type.