Useful Array Functions for VBA, Part 4

PHP has a function for adding values to the beginning of an array. Let's look at a few functions that let us append and prepend values to an array.

Append a Value to Each Member of an Array

Let's say we have an existing array and need to append a value to each element. For example, an ID number or a date value. This function loops through a passed array and appends the value.

Function Array_AppendEach(arr As Variant, _
    valueToAppend As Variant) As Variant
  Dim tempArray As Variant
  Dim i As Long
  Dim j As Long

  tempArray = arr

  If NumberOfDimensions(tempArray) > 1 Then
    For i = LBound(tempArray) To UBound(tempArray)
      For j = LBound(tempArray, 2) To UBound(tempArray, 2)
        tempArray(i, j) = tempArray(i, j) & valueToAppend
      Next j
    Next i
  Else
    For i = LBound(tempArray) To UBound(tempArray)
      tempArray(i) = tempArray(i) & valueToAppend
    Next i
  End If

  Array_AppendEach = tempArray
End Function

This function uses the NumberOfDimensions function we introduced in a previous article. It assumes that the array being passed is either one or two dimensional.

I chose the simplest way to do this. We could have also declared an empty array, resized it to the same size as the original array, then wrote each array value to the new array (no word on which method is faster).

Sample Usage

Using functions from the previous articles, let's append a value to each element in an array.

Dim arr As Variant
  arr = Array_Create(5, 2)
  arr = Array_Fill(arr, "apple")
  arr = Array_AppendEach(arr, " banana")

Prepend a Value to Each Member of an Array

Prepending a value is almost the same procedure, except the value is placed at the beginning of each element instead of at the end.

Function Array_PrependEach(arr As Variant, _
    valueToPrepend As Variant) As Variant
  Dim tempArray As Variant
  Dim i As Long
  Dim j As Long

  tempArray = arr

  If NumberOfDimensions(tempArray) > 1 Then
    For i = LBound(tempArray) To UBound(tempArray)
      For j = LBound(tempArray, 2) To UBound(tempArray, 2)
        tempArray(i, j) = valueToPrepend & tempArray(i, j)
      Next j
    Next i
  Else
    For i = LBound(tempArray) To UBound(tempArray)
      tempArray(i) = valueToPrepend & tempArray(i)
    Next i
  End If

  Array_PrependEach = tempArray
End Function

As with the previous function, this one assumes that the array is one or two dimensional.

Sample Usage

Dim arr As Variant
  arr = Array_Create(5, 2)
  arr = Array_Fill(arr, "apple")
  arr = Array_PrependEach(arr, "banana ")

Add Values to the Beginning of an Array

This is a VBA version of the array_unshift method. It increases the size of the array and inserts new values into the empty elements.

Function Array_Prepend(arr As Variant, _
    ParamArray valuesToPrepend() As Variant) As Variant
  Dim tempArray As Variant
  Dim i As Long

  ' make temp array the same size as 
  ' the current array + number of new elements
  ReDim tempArray(LBound(arr) To _
    (UBound(arr) + UBound(valuesToPrepend) + 1))

  ' put new elements at the beginning of the new array
  For i = LBound(valuesToPrepend) To UBound(valuesToPrepend)
    tempArray(i) = valuesToPrepend(i)
  Next i

  ' put existing elements after the new ones
  For i = UBound(valuesToPrepend) + 1 To UBound(tempArray)
    tempArray(i) = arr(i - (UBound(valuesToPrepend) + 1))
  Next i

  Array_Prepend = tempArray

End Function

The input array must be one dimensional. My favorite part of the function is that you can add values using a ParamArray. We could have also used a traditional array and simply looped through it to get the values to add, but looping through a ParamArray works exactly the same (and provides another tool for our use).

Sample Usage

Dim arr As Variant
  arr = Array_Create(5)
  arr = Array_Fill(arr, "apple")
  arr = Array_Prepend(arr, "banana", "orange", "peach")

In the next article we'll look at checking arrays for unique values and differences.

Update 1/4/2013: Rick Rothstein sent me a function that does the same thing as my code above, albeit as a one-liner:

Function Array_Prepend(arr As Variant, _
         ParamArray valuesToPrepend() As Variant) As Variant
  Array_Prepend = Split(Join(valuesToPrepend, Chr(0)) & _
                        Chr(0) & Join(arr, Chr(0)), Chr(0))
End Function

Sub Test()
  Dim X As Long, arr As Variant
  arr = Split("one two three four")
  arr = Array_Prepend(arr, "banana", "orange", "peach")
  
  ' Let's see the result
  For X = 0 To UBound(arr)
    Debug.Print arr(X)
  Next
End Sub