Useful Array Functions for VBA, Part 3

In the previous article, we reviewed some VBA functions that populate arrays. In this article we look at a function to resize arrays as well as checking their size.

Return the size of an array

Yes I know we can just use UBound, but where's the fun in that? This function lets you specify which dimension you want to check.

Function Array_Size(arr As Variant, _
    Optional whichDimension As Long = 1) As Long
Array_Size = UBound(arr, whichDimension) + IIf(LBound(arr) = 0, 1, 0)
End Function

This is the same as UBound(arr) + 1 for zero based arrays, and UBound(arr) for one based arrays. It returns the number of available elements in the array. This function is one based, so if you pass in an array in which LBound(arr) = 0 and UBound(arr) = 9, it will return 10.

It also accepts one based numbers as the dimension you want to check. Ex: If you want the number of available "columns", use Array_Size(arr, 2) for the second array dimension. This assumes, of course, that the array has more than one dimension.

Resizing an array

Resizing arrays is difficult because you have some decisions to make:

• Do you want to keep the existing values of the array?
• Do you want to resize columns, rows, or both?
• Do you want to increase or decrease the size of the array?

The following function is what I came up with to resize an existing array:

Function Array_Resize(arr As Variant, _
                      numRows As Long, _
                      Optional numCols As Long) As Variant
  Dim tempArray As Variant
  Dim secondArray As Variant
  Dim i As Long
  Dim j As Long
  ' make a copy of the array
  tempArray = arr

  If NumberOfDimensions(tempArray) > 1 Then  ' 2D
    ' size the second array accordingly
    If numCols > 0 Then
    ReDim secondArray(LBound(tempArray) To numRows - 1, _
                      LBound(tempArray, 2) To numCols - 1)
    Else
      ReDim secondArray(LBound(tempArray) To numRows - 1)
    End If
    
    If NumberOfDimensions(secondArray) > 1 Then
    ' populate the resized array with the existing values
    For i = LBound(secondArray) To UBound(secondArray)
      For j = LBound(secondArray, 2) To UBound(secondArray, 2)
        On Error Resume Next
        secondArray(i, j) = tempArray(i, j)
        If Err.number = 9 Then  ' no more elements
          Exit For
        End If
      Next j
    Next i
    Else
      For i = LBound(secondArray) To UBound(secondArray)
        On Error Resume Next
        secondArray(i) = tempArray(i, LBound(tempArray, 2))
        If Err.number = 9 Then ' no more elements
          Exit For
        End If
      Next i
    End If
  Else  ' 1D
    ' do we want to add columns to the array?
    If numCols > 0 Then
      ReDim secondArray(LBound(tempArray) To numRows - 1, _
                        LBound(tempArray) To numCols - 1)
    Else
      ReDim secondArray(LBound(tempArray) To numRows - 1)
    End If

    ' populate the resized array with the existing values
    If NumberOfDimensions(secondArray) > 1 Then
      ' populate 2D array with elements from first column only
      For i = LBound(secondArray) To UBound(secondArray)
        On Error Resume Next
        secondArray(i, LBound(secondArray, 2)) = tempArray(i)
        If Err.number = 9 Then  ' no more elements
          Exit For
        End If
      Next i
    Else  ' loop through rows only
      For i = LBound(secondArray) To UBound(secondArray)
        On Error Resume Next
        secondArray(i) = tempArray(i)
        If Err.number = 9 Then ' no more elements
          Exit For
        End If
      Next i
    End If
  End If

  Array_Resize = secondArray
End Function

This function always preserves the existing values of an array; use Array_Create if you want an empty array.

You can resize both rows and columns, either up or down. If you remove rows or columns, any data in the removed rows or columns is lost. Use Array_Size to programmatically determine the size of the array.

Sample Usage

Using the functions from the previous articles, let's create a small array and then pass it through the above function to see what happens.

Dim arr As Variant
  arr = Array_Create(5, 2)
  arr = Array_Range(0, 10, 2)

Now we have a one dimensional array with six rows. I want to resize the array by doubling the number of rows and columns. We can do it like this:

If NumberOfDimensions(arr) = 1 Then
  arr = Array_Resize(arr, Array_Size(arr) * 2)
Else
  arr = Array_Resize(arr, Array_Size(arr) * 2, Array_Size(arr, 2) * 2)
End If

Since we started with a one dimensional array, we now have an array with twelve rows (index 0 to 11). Here's how it would work with a two dimensional array:

Dim arr As Variant
  arr = Array_Create(5, 2)
  ' populate 2D array
  arr = Array_Fill(arr, "apple")
  If NumberOfDimensions(arr) = 1 Then
    arr = Array_Resize(arr, Array_Size(arr) * 2)
  Else
    arr = Array_Resize(arr, Array_Size(arr) * 2, Array_Size(arr, 2) * 2)
  End If

This sequence takes us from a 2D array with five rows and two columns to a 2D array containing ten rows and four columns. See?

Beat part is, the first five elements still have the word "apple" in them. We made the array bigger while preserving the existing elements.

From this point I'll simply post a few examples of resizing the array we already have.

' resize 2D to 1D (add five more rows)
  arr = Array_Resize(arr, Array_Size(arr) + 5)
  ' resize 1D to 1D (only 3 rows)
  arr = Array_Resize(arr, 3)
  ' resize 1D back to 2D (3 rows, 3 cols)
  arr = Array_Resize(arr, 3, 3)
  ' resize 2D to 2D (add 2 cols)
  arr = Array_Resize(arr, Array_Size(arr), 5)
  ' resize 2D to 2D (double rows, same number of cols)
  arr = Array_Resize(arr, Array_Size(arr) * 2, Array_Size(arr, 2))
  ' resize 2D to 2D (more rows and more columns)
  arr = Array_Resize(arr, Array_Size(arr) + 1, Array_Size(arr, 2) + 2)
  ' resize 2D to 2D (less rows and less columns)
  arr = Array_Resize(arr, Array_Size(arr) - 2, Array_Size(arr, 2) - 5)
  ' resize 2D to 2D (more rows and less columns)
  arr = Array_Resize(arr, Array_Size(arr) + 10, Array_Size(arr, 2) - 1)
  ' resize 2D down to 1D (back to 3 rows)
  arr = Array_Resize(arr, 3)
  ' current number of rows?
  Debug.Print Array_Size(arr)

In the next article we'll look at appending values to each element in an array.