Useful Array Functions for VBA, Part 2

In the previous article, we reviewed a VBA function that creates arrays for us. Now let's look at two functions that help us populate them.

Filling an Array

PHP has an array_fill method which fills an array with a certain quantity of a given value, starting at a certain index position.

We will follow this model, however the parameters will be reordered and we will be starting the array at whatever index is specified by the existing array.

• First parameter: the array itself
• Second parameter: value to insert in each element

Function Array_Fill(arr As Variant, value As Variant) As Variant
  Dim tempArray As Variant
  Dim i As Long
  Dim j As Long

  tempArray = arr

  If NumberOfDimensions(arr) = 1 Then
    For i = LBound(tempArray) To UBound(tempArray)
      tempArray(i) = value
    Next i
  Else
    For i = LBound(tempArray) To UBound(tempArray)
      For j = LBound(tempArray, 2) To UBound(tempArray, 2)
        tempArray(i, j) = value
      Next j
    Next i
  End If

  Array_Fill = tempArray
End Function

This function works with both one and two dimensional arrays. I don't know why PHP lets you start the index at a number other than zero or one, but maybe they know something I don't. I strongly prefer to start the index at either one or zero.

To create the array, use whatever method you prefer. I posted a method in the previous article for creating arrays using a custom function.

The NumberOfDimensions function mentioned above is something I found on Stack Overflow:

Private Function NumberOfDimensions(ByVal vArray As Variant) As Long
  Dim dimnum As Long
  Dim errorCheck As Long
  On Error GoTo FinalDimension

  For dimnum = 1 To 60000
    errorCheck = LBound(vArray, dimnum)
  Next dimnum

FinalDimension:
  NumberOfDimensions = dimnum - 1
End Function

It simply tries to access every possible array dimension. When it throws an error, we know the last known good array dimension is the size of the array.

Sample Usage

Ex: You want an array (two dimensional) with 5 elements (index 1 through 5) filled with "apple":

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

Ex: You want an array (one dimensional) with 5 elements (index 0 through 4) filled with "banana":

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

Filling an Array with a Range of Values

PHP has a range method for populating an array with an incrementing range of values. (Note to PHP authors: why is this not called "Array_Range"?)

This method will only work with one dimensional arrays, and only works with numbers. At some future date (translation: when I figure it out) I'll adapt the function to take strings. Otherwise it seems pretty close to the PHP function.

Function Array_Range(startingValue As Long, upperbound As Long, _
    Optional Step As Long = 1) As Variant
  Dim tempArray As Variant
  Dim tempValue As Long
  Dim i As Long

  If startingValue = 0 Then
    ReDim tempArray((upperbound / Step))
  Else
    ReDim tempArray(((upperbound / Step) - (startingValue - 1) - 1))
  End If

  ' value of first array element is starting value
  tempValue = startingValue

  For i = LBound(tempArray) To UBound(tempArray)
    tempArray(i) = tempValue
    tempValue = tempValue + Step
  Next i

  Array_Range = tempArray

End Function

This function wipes out an existing array (if any) and overwrites it with a range of values as specified by the input parameters. If you do not specify a step, it increments numbers by one with each iteration.

Sample Usage

Ex: You want to create an array that holds the numbers 1 through 10.

Dim arr As Variant
arr = Array_Range(1, 10)

Ex: You want to create an array that holds the numbers 1 through 10, skipping every other number.

Dim arr As Variant
arr = Array_Range(1, 10, 2)

The output might not be exactly what you expect. The above returns "1, 3, 5, 7, 9" because if you count by 2s and start from 1, the next number in the sequence is 11 which is greater than the upper bound we specified. The function will not go beyond the upper bound even if doing so leaves the upper bound out of the array. Obviously, it works best with certain number sequences such as

Dim arr As Variant
arr = Array_Range(0, 100, 10)

Next article we'll talk about resizing arrays.