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.

I wonder, for your first function, would it not be simpler to say

This is a little cleaner, plus it covers you if for any base, not just base 0 or 1.

Try this

For arrays with negative bounds, use