What is an Array?

An array, like a variable is a somewhere to store data.  An array can however hold more than one value, for example an entire database of information with tens of thousands of records.

Using arrays in your procedures makes your code potentially quicker than alternative methods for say performing calculations on large datasets or transferring information from one sheet to another.

Declaring an Array

You declare arrays in a similar way to the way you declare variables.  When you declare an array you also state the number of elements that the array will contain.

For example…

The MyArray array contains six elements – yes six not five: 0, 1, 2, 3, 4, 5. So by default the array index starts at zero.

If you need the index count to start at 1, type Option Base 1 at the top of the module (before an procedures).

You can also specify the number of elements in an array by declaring its lower and upper bounds. For example..

Each element in an array is like a pocket that you can store a separate piece of information in. If you are going to store the same type of data(long, integer, string etc) in each element then state that data type when you declare the array.  If different types of data are going to be stored in different elements use Variant as your data type.

Declaring Multidimensional Arrays

The previous examples involved one dimensional arrays, equivalent to a single row or column of values.  If you need to store a table of data you would need a two dimensional array as tables are made up of rows and columns.  If you had a cube of data then you would require three dimensions.  Luckily arrays are not restricted to single dimension, in fact an array can include up to sixty dimensions (that takes some thinking to visualise!).

This array is equivalent to a table containing ten rows and five columns. It contains fifty elements.

This is an example of a three dimensional array containing one hundred and twenty-five elements.

Referencing Elements in an Array

Assigning Values

You can reference each element in an array using its index number.  For example to assign a value to an element…

…or in a two dimensional array.

To assign values stored in a worksheet…


This will store all data in the specified region in the array.

Using this method there is no need to declare the number of elements that the array will need to include.

Returning Values

To reference or return a particular value in an array, specify the array element’s index number/s as shown below.

This would return the value 10, which is in the fifth row (first dimension) and second column (second dimension) of the array.

Perform Calculations on an Array

In the example below the average is calculated for all values held in MyArray.

In this example the sum of values in the second row of the array is calculated

Redimensioning an Array

The ReDim statement allows you to redimension an array. In the example below we have added another row of data to the worksheet array and assigned values to each element in that new row.

Follow the ReDim statement with the new size of your array.

When you ReDim you erase all values currently stored in the array.  That is unless you use the keyword Preserve.

Preserve howevers adds another problem – when using it you can you can only resize the last dimension of the array. As we want to resize the first dimension (ie add rows) we need to transpose the data so it is held in the array as a horizontal table. This is easily achieved as shown below.

Once we have increased the size of the array we can assign new values.

Then reverse transpose the array.

The code in full..

The Locals window shows the new values have been successfully assigned to the new elements in our array.

Locals Window

 LBound and UBound

The LBound and Ubound functions return the starting and ending index number for a specified array dimension.

=LBound(arrayname, dimension)

In this example the LBound and Ubound functions are used to display the first and last index number for each dimension in our array.

Results are shown below…


In this example we redimension myarray to include an extra column where we can store a sum of values for each row.  We then loop through each row using the Lbound and Ubound functions to determine the number of loops to perform.  Each loop calculates the sum of values in that row and stores the answer in the new column.  Finally all the answers are spewed out into the next available column in our worksheet.

Results are shown below…

Dynamic Arrays

Sometimes your array will need to grow in size to an unknown upper bound as your code progresses.  In our example the procedure will loop through a large database and store Brighton transactions in an array.  The size of the array can only be known once the procedure has looped through all transactions, so we need someway of growing the size of the array whenever a Brighton transaction is found.

Here’s a snapshot of the database – it has about 20,000 records.

We want to transfer all transactions relating to the Brighton branch to another sheet.

To achieve this using arrays we must first of all store the entire database in an array…

Next we loop through each record in the database checking to see if the transaction relates to the Brighton branch.

If it does we keep a tally of the number of Brighton records in a variable called BtnTransactionCounter

We can then use this variable to redimension an array which will hold our Brighton transactions. Remember you can only redimension the last dimension when you use the Preserve keyword so we have had to store the data in a horizontal table. When we spew the data out on another sheet we will need to transpose this back to a verticle table.

We then need to populate our BrightonTransactions array with the values in the current transaction record.  There are eight fields so we need to loop eight times to achieve this.

That’s the If statement completed so onto the next transaction – and so on to the end of the database.

To transfer the data to another sheet, first we create the sheet.

Then we add the field headings contained in the Transaction array.

Next spew the data out into the new sheet – transposing it into a vertical table.

Finally autofit columns.

The complete code…

Posted by Chester Tugwell