20 Arrays

,

20.1 One-dimensional arrays

Suppose we need to store the name of a student in our program. No problem, we’ll use a variable of type String. But what if we need to save the names of 30 students? Should we use 30 variables of type String? Well, we could, but there is a better way. We can declare an array. An array can be regarded simply as a table with room for a number of data items, all of the same type. In the case at hand we would need an array with room for 30 names of type String. Each name in the array is called an array element. To declare such a structure we write:

It is almost like a simple variable declaration with the exception of the parentheses after the variable name. What we are saying here is that we want to declare a variable with the name students with room for 31 names (Strings).¹ In contrast, the statement below declares a variable with room for one name only:

How can we refer to each element (name) in the students array? We simply refer to the element by using its position in the array written in parentheses after the array name. This is called an index. Indices are integers starting at 0 for the first element and increments by 1 for each new element (figure 20.1).

Figure 20.1 Array after insertion of an element at position 2.

Students(0) = “Tom Jones”	' Store a name at index 0
Students(1) = “Kate Beckinsale”	' Store a name at index 0
Debug.Print students(2)	' Print the name at index 2

The array can now be visualized as shown in figure 20.2.

Figure 20.2 students array after insertion of three elements.

Let’s explain why the students array has room for 31 names, and not only 30. The number 30 is not the maximum number of elements, but actually the index of the last element. Since the indices start with 0 and end with 30 there is room for 31 elements.

We can consider an array to be a collection of many simple variables, and we can use an array element wherever we can use a simple variable of that type in our program.

Declaring arrays

Let’s take a closer look at array declarations. The general syntax is:

or

Lower limit and upper limit are the smallest and greatest value the index can have. If only the upper limit is specified as in the first case it is assumed that the lower limit is 0 (it may also be 1 – see the Options Base 1 section below).

Examples

In the first line an array with space for 11 integers is defined. Valid indices are from 0 to 10 (inclusive). The second array has room for 20 floating point numbers with indices from 1 to 20. The third line defines an array with space for 101 strings and an array with space for 25 Booleans. The fourth line shows that indices also may be negative.

If As Type is not specified a Variant is assumed.

Arrays are declared the same way as other variables, using the Dim, Static, Private, or Public keywords.

Option Base 1

By writing Options Base 1 at the top of the code module we’re telling VBA that indices will start at 1 in case the lower limit is missing from the array declaration.

Option Base 1 only applies to the module where the directive is written.

Working with arrays

A value can be assigned to a simple variable at the same time as the variable is created. This is called initialization. In VBA numeric variables are initialized to 0, string variables to an empty string² (whose length is zero), Boolean variables to False, and Variants to Empty. The same goes for arrays. If we define an array like this:

then every element will be initialized to 0.

To assign another value to an array element you need to write an assignment statement:

Sometimes we may be able to use a loop:

It is not allowed to copy the whole array like this:

We must copy element by element:

An array index can be an expression of any integral type. To create a valid reference to an array element you must make sure that the value of the index lies between the minimum and maximum indices specified for the array. All these statements are valid:

If you try to reference an array element with an invalid index, VBA will display an error message.

Figure 20.3 Error message.

There is a close connection between a For…Next statement and an array. Quite often we need to do something with every element in the array, and a For…Next loop is particularly suited for this purpose. The counter variable in the loop is chosen so that it can be used as an index into the array. The next example shows how we can fill an array with values from a worksheet range. Figure 20.4 show some stock values for a few stocks on the London Stock Exchange. Our goal is to read the prices into an array.

Figure 20.4 Price of stocks.

Another common operation done to arrays is changing the order of the elements. The most common operation where this becomes a necessity is sorting. In our next example we will show you how you can reverse the array elements so that the first element becomes the last element, the second element becomes the second to last element, and so on (see figure 20.5).

Figure 20.5 Reversing the order of the array elements.

We solve the problem by using two indices: one that starts at the beginning of the array, and one that starts at the end. For each pair of elements that are swapped we increase the first index by 1 and decrease the last by 1. This is repeated until the last index is equal to or less than the first one. If there is an odd number of elements in the array the element in the middle need not be moved. As you saw in chapter 19 we will need an extra helper variable to swap two elements. The code is:

An alternative would be to use the Swap function from chapter 19.

Some useful array functions

Suppose an array is defined like this:

To reference the elements in this array you can use For…Next loops, for instance

Now, suppose you have many loops like this spread throughout your code and you decide to change the definition of the array to

This requires that you investigate the whole program to make sure the loops are correct.

VBA has two functions LBound and UBound that can be used to find the smallest and greatest indices an array can have. If we change the For…Next loop above to

then the loop would be correct even if we changed the size of the array. The syntax for LBound and UBound is

For a one-dimensional array, dimension is equal to 1 and may be dropped. For a two-dimensional array dimension is equal to 1 for the first dimension and 2 for the second.

The function Array creates a variant that contains an array of elements from a list of values. The syntax is:

valuelist is a comma-separated list of values. The first value is stored in the first element in the table and so on. An empty value list creates an empty table.

A variable of the type Variant can contain an array of any type except fixed-length strings and user-defined types (not treated in this book).

Transfer data between a worksheet and an array

There are two ways to fetch data from a worksheet and store it in an array. In the first method we use a loop and read one value at a time. An example of this is given in the section on working with arrays. The other method is to assign a cell range to a Variant. Here’s how:

The first index is always 1 if you use this method. The array may now be referenced

Which method should you use? That depends on how the array is used in your program. It takes more time to read the data with the first method, but it takes less time to manipulate elements in an array versus a Variant. If this is of no great concern either method will do. The reason that the second method is faster when it comes to reading data is that it does not actually copy the data. It just points to the data in the worksheet.

To copy data from an array to a worksheet the Excel function Transpose is used:

The values are written into the cells A1:A10.

20.2 Multi-dimensional arrays

Two-dimensional arrays

Two-dimensional structures are very common in everyday life. Timetables, calendars and a board of chequers are a few examples. VBA lets us declare arrays with several dimensions and sizes. Here’s how we declare a two-dimensional array with three rows and four columns:

The first number (2) represents the largest index for a row and the second (3) represents the largest index for a column. If the indices start at 0 this will create an array with three rows and four columns.

Figure 20.6 Array with 3 rows and 4 columns.

Another example shows how we can represent a noughts and crosses board with an array:

The type is a fixed-length String with length 1. An illustration of the array is shown in figure 20.7.

To reference every element in a two-dimensional array we often use two For…Next loops. The outer loop can iterate over the number of rows in the array and the inner loop can iterate over the number of columns. For each iteration of the outer loop the inner loop does all its iterations. Here’s how we can print the noughts and crosses board:

Figure 20.7 Noughts and crosses board after the code above has executed.

Arrays with more than two dimensions

Arrays with more than two dimensions can also be defined, but arrays with more than three or four dimensions are rare. To define an array with three dimensions we write index ranges for each dimension separated by commas.

An element in this array can be referenced by using three indices:

20.3 Dynamic arrays

It isn’t always possible to foresee the size of an array. Such cases can be elegantly handled by declaring a dynamic array. A dynamic array is an array that may grow or shrink dynamically as the program is running. The syntax for declaring a dynamic array is:

We use an empty pair of parentheses. In this way we tell VBA that we want to declare an array but we do not know the size yet. Before we start using the array we must provide the size. This is done with a ReDim statement.

ReDim lets us change the size (and also the lower and upper indices), but does not allow us to change its type.

This is allowed:

but this is not:

What happens when we’re using ReDim on an array that is filled with data? Normally, the data that is stored in the array is lost. The elements are initialized to 0 (for numbers), empty strings (for String), False (for Booleans), Empty (for Variants), and Nothing (for Objects). If we want to keep the old values we must specify the reserved word Preserve³ after ReDim as in

The Erase function

The Erase function can also be used to reinitialize an array. The syntax is

If the array is a dynamic array the memory occupied by the array will be freed. This does not happen if the array is a static array. In this case the elements are initialized to 0 (for numbers), to empty strings (for String), to False (for Boolean), to Empty (for Variants) and to Nothing (for Objects).

20.4 Arrays as parameters

Arrays can be transferred in and out of functions and subroutines. However, it must always be a reference parameter and hence implicitly transferred both in and out of the procedure.

The array parameter is declared as a dynamic array. This will enable us to call the function with actual arrays of different sizes.

If you want to create a procedure that can take an array of any length and element type, use a Variant as parameter. This works because a Variant is able to hold an array as its value.

A function may also return an array as its result. The return type must be a dynamic array as shown below:

The result must be stored in a dynamic array of the same type as the function value.

Since variables of type Variant can contain an array, we can let the function return the array as a variant.

For Each…Next used on arrays

The For Each…Next loop can be used on arrays

The For Each control variable on arrays (elem) must always be a Variant.

Procedures with an indefinite number of arguments

Generally, when calling a procedure, the number of arguments must be the same as the number of parameters. There is a way around this, though. A procedure can accept an arbitrary number of arguments if we use the ParamArray keyword in the parameter list. A ParamArray is declared as an array of Variant type

ParamArray can be used only on the last parameter of a parameter list.

To call such a procedure we supply a comma-separated list of values:

The following example creates a function that returns the sum of all the arguments.

It can be called like this:

What’s the difference between using a ParamArray versus an array parameter? A procedure with an array parameter

can only be called with an array of double as argument. Using a ParamArray we can call the procedure with a number of arguments of different types.

20.5 Sorting and searching

Two common operations performed on arrays are sorting and searching. Searching means “looking through” the array to find a particular value, and sorting means ordering the elements in a special sequence. There are many different algorithms one can use to handle these two operations. They vary in efficiency and ease of understanding. We’ll begin by looking at a simple search operation – finding the smallest value in the array. The problem will be solved by a linear search.

Linear search

In a linear, or sequential, search we investigate each element in turn, starting with the first one. The search is closed when we find the element we are looking for, or the end of the array is reached. To search for the smallest element we’ll use the following algorithm:

We’ll code the search in a function MinIndex. The function will need an array parameter (the array to be searched) and return an integer (the index of the smallest element). In addition we have added two parameters – first and last. They store the indices of the first and last elements to be searched. Specifying indices makes it a more general function as it allows us to search only a part of the array as well as to the whole array.

A similar function that finds the index of a particular value instead of the smallest one can be declared like this:

Here arr is the array to be searched and value is the value we’re looking for. If the value is not found the function will return−1.

The algorithm can be formulated as:

Using a For…Next loop and an If…End If statement this can be implemented as:

Binary search

If an array is sorted we can utilize a much more efficient search algorithm called binary search. It is similar to the way we look up a phone number in a phonebook. If we are looking for the phone number of a person whose name begins with P and we open the phonebook on a page with names beginning with H, we know that there is no point in searching through the previous pages in the phonebook. We continue the search in the last part of the book. By repeating this procedure we drastically reduce the number of pages we have to search through.

Here’s the algorithm.

It can be implemented like this:

Binary search can be significantly faster than linear search, particularly when the data set is very large.

Selection sort

Selection sort is a simple sorting algorithm that is easy to understand. It’s similar to the way we sort a hand of cards. It is not among the most efficient general sorting algorithms, though.

The idea of the algorithm is to divide the array into two parts – a sorted part and an unsorted part. At the beginning the sorted part is empty and the unsorted part is the whole array. The sorting is done like this (see also figure 20.10):

We have already written subroutines to find the index of the smallest element in part of an array, and to swap two elements. If we utilize those two subroutines the sorting subroutine will be easy to write.

This is an example of divide-and-conquer as well as code reuse. We have split the main problem into smaller problems and solved each one independently. Also, using written, tested (and hopefully error-free) procedures saves us time and reduces the risk of errors.

Figure 20.10 Selection sort. The sorted part of the table is shaded.

A small test procedure is written below. An example of a test run is shown in figure 20.11.

Figure 20.11 Result after running the procedure Test above.

Problems

20-1.  A car dealer has a simple worksheet showing the total sales for a year.

Write a subroutine that calculates the total sale per month, and write the result into the worksheet beginning in column F, row 3.

20-2.  A random integer from 1 to N can be generated with the expression

Use this expression to write a function that generates K random numbers in the range 1 to N. N and K will be parameters to the function. The function will return an array of K numbers.

Since the expression may generate two or more equal numbers, make sure that no duplicates are stored in the array.

Before calling Rnd, use the Randomize statement without an argument to initialize the random number generator with a seed based on the system timer.

20-3.  In a magic square the sum of each row, each column and of both the corner diagonals adds up to the same number (see example below). Write a function that checks whether an N × N array of integers is a magic square. The function should return True (is magic square) or False.

Notes

1  31 is not a typo. See explanation in the text.

2  Fixed-length strings are initialized to a string of zeros.

3  If Preserve is used on a multi-dimensional array, only the last dimension can be changed.