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.

## Follow Me