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.
- 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
, andcolB
, which will contain the number of rows and columns in each matrix. - Next we store the input values as arrays called
MatA
andMatB
using theRange.Value
procedure. Again these have to be of Variant type. - Next we use the
UBound
function to work out the number of rows and columns in each matrix. As we used theRange.Value
method to define our arrays, we know that the ranges are numbers from 1 up to theUBound
values. - 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. - Next we check whether the product is defined, and if it is not we set
MatrixMult
equal to an appropriate error message. - If the product is defined, then we know that the answer is a (
rowA
\(\times\)colB
) matrix, so we canReDim
the arrayAns
to be this size. - Next we use a pair of nested loops to calculate
Ans(i,j)
for each possible value ofi
andj
. To do this we use a third loop to calculate the result. - Finally, we set the value of
MatrixMult
equal to this function. - 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])