Chapter 11 Arrays

11.1 Dynamic arrays

We have seen in the lectures how to define an array of a fixed size. However, there are times when we do not know in advance what size an array will be. Or we may want to change the size of the array as we run our program. To do this we use dynamic arrays.

To declare a dynamic array we use an empty set of parentheses:

Dim ArrayName() As type

However, before we can use this array we will need to tell VBA what size it is using a ReDim statement. Typically this size will depend on one or more variables from our program (as otherwise we would have fixed the size in advance). For example, if we want the array to range from 1 to max then we would include the line

ReDim ArrayName(1 To max)

This is the only way to define the range of an array that depends on a variable.

If we want to change the size of the array later in the program, we can use the same ReDim command again. However, doing so will delete all of the currently stored values in the array. If we want to keep the existing values when we change the size we have to use the command

ReDim Preserve ArrayName(1 To newmax)

However even with the Preserve command, any elements in the old array that are no longer elements in the new array will disappear. For example if we have a dynamic array X with entries X(3), X(4) and X(5), and we then use the command

ReDim Preserve X(4 To 6)

then X will now keep the old entries X(4) and X(5), gain a new (and as yet unassigned) entry X(6) but lose the entry X(3).

11.2 Inputting an array, and some useful array functions

There are various methods of entering an array, and the best one to use will depend on the nature of the problem and the amount of data. In lectures we saw how to use the Array command, and the Range.Value method. Both of these required the array to be of Variant type, and so cannot directly be used to fill an array of known dimensions. We typically fill such an array using one or more loops, as we will illustrate later in this section. Before considering loops, we will review in more detail some useful commands for dealing with matrices of Variant type.

Once we have defined an array we will typically need to manipulate its entries, and so we will usually need to know the total number of rows and of columns. If the array was of an unknown size (for example of Variant type) then we need commands to find out this information.

VBA provides two very useful commands: UBound and LBound, which tell us the upper and lower bounds of the array. The syntax for these commands is LBound(array, dimension) and UBound(array, dimension) where array is the name of the array, and dimension is the dimension we are interested in. (The dimension argument is not needed for a one dimensional array.) Note that when we use the Range.Value method for entering an array, it is always two dimensional, even if the range only consists of one row or one column. For example, if we run the code

Function dummy()
Dim TestArray As Variant
Dim xUpper As Integer, xLower As Integer
Dim yUpper as Integer, yLower As Integer
TestArray = Range("B3:D9").Value
xUpper = UBound(TestArray, 1)
yUpper = UBound(TestArray, 2)
xLower = LBound(TestArray, 1)
yLower = LBound(TestArray, 2)
dummy = xLower
End Function

then we will have xUpper=7, yUpper=3, xLower=1, yLower=1 as there are 7 rows and 3 columns in the given range B3:D9.

To illustrate the various structures and commands described above, consider the problem of multiplying two matrices.

Excel provides a function called MMULT which calculates the product of two matrices. The command has syntax =MMULT(array1, array2) so if we have \(2\times 2\) matrices in cells A1:B2 and D1:E2 then =MMULT(A1:B2, D1:E2) will calculate their product.

Notice that the output of MMULT is an array, so how do we see it if the function is entered in a single cell? The answer is that we must prepare for an output of the appropriate dimensions before we enter the function, and then enter the function into the collection of cells where we want the answer to appear.

To do this you first highlight the cells where the answer should appear. In the example we know the output should be a \(2\times 2\) array so we would highlight some square of four cells. Then we type the function in as above, but instead of finishing by pressing Enter we must finish with Ctrl+Shift+Enter.

To illustrate the various properties of arrays described so far, let us try to write our own version of a matrix multiplication function. We will make a function that takes two rectangular ranges of cells from the spreadsheet as inputs, and outputs their product (if it exists) or a suitable error message.

Function MatrixMult(A As Variant, B As Variant) As Variant
Dim rowA As Integer, colA As Integer
Dim rowB As Integer, colB As Integer
Dim rowC As Integer, colC As Integer
' A and B are ranges, so we convert them into arrays MatA, MAtB
MatA = Range(A).Value
Mat(B) = Range(B).Value
' rowA is the number of rows, colA the number of columns, in matA
' rowB and colB similar for matB
rowA = UBound(MatA, 1)
colA = UBound(MatA, 2)
rowB = UBound(MatB, 1)
colB = UBound(MatB, 2)
'Ans will contain our final product
'It is a dynamic array as we do not know its dimension
Dim Ans() As Double
Dim i As Integer, j As Integer, k As Integer
If Not (colA = rowB) Then
    MatrixMult = "Product not defined"
Else
ReDim Ans(1 To rowA, 1 To colB)
For i = 1 To rowA
    For j = 1 To colB
        Ans(i,j) = 0
        For k = 1 To colA
            Ans(i, j) = Ans(i, j) + MatA(i, k) * MatB(k, j)
        Next k
    Next j
Next i
MatrixMult = Ans
End If
End Function

This is the longest program that we have seen so far, and relatively complicated, so we will discuss the different aspects of it in turn.

  1. We want to input two arrays. I have defined these as Variant objects, and one of the problems that we have is that we do not know their dimensions in advance. The first thing we do is define variables called rowA, colA, rowB, and colB, which will contain the number of rows and columns in each matrix.
  2. Next we store the input values as arrays called MatA and MatB using the Range.Value procedure. Again these have to be of Variant type.
  3. Next we use the UBound function to work out the number of rows and columns in each matrix. As we used the Range.Value method to define our arrays, we know that the ranges are numbers from 1 up to the UBound values.
  4. The product (if it exists) will be stored in an array called Ans. We define this as a dynamic array as we do not know in advance its dimensions.
  5. Next we check whether the product is defined, and if it is not we set MatrixMult equal to an appropriate error message.
  6. If the product is defined, then we know that the answer is a (rowA\(\times\)colB) matrix, so we can ReDim the array Ans to be this size.
  7. Next we use a pair of nested loops to calculate Ans(i,j) for each possible value of i and j. To do this we use a third loop to calculate the result.
  8. Finally, we set the value of MatrixMult equal to this function.
  9. To run this code we again have to first highlight the appropriate sized rectangle of cells, and then enter the formula with the two arrays in quotes, for example =MatrixMult(“A1:C3”, “E1:G3”) before finishing with Ctrl+Shift+Enter.

To conclude this section, we briefly mention two useful functions which convert between strings and one dimensional arrays. Given a string, the Split function returns an array consisting of substrings from the original string separated by a delimiter (which can be specified). The syntax is

Split(expression [,delimiter] [,limit])

where expression is a string, delimiter is an option argument giving the character which separates the substrings (the default is space), and limit is the maximum number of substrings to be returned (the default is all).

Thus the command

x = Split("Monday Tuesday Wednesday")

produces an array x with x(1)="Monday", x(2)="Tuesday", x(3)="Wednesday". Similarly

x = Split("Monday,Tuesday,Wednesday,Thursday", ",",3)

produces an array x with x(1)="Monday", x(2)="Tuesday" and x(3)="Wednesday,Thursday".

The Join function reverses this process, and turns an array of strings into a single string separated by a specified delimiter (which by default is the space). This has syntax Join(sourcearray[, delimiter])