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…
Dim MyArray (5) as Integer
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..
Dim MyArray (1 to 5) as Long
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.
Dim MyArray (1 to 10, 1 to 5) as Variant
This is an example of a three dimensional array containing one hundred and twenty-five elements.
Dim MyArray (1 to 5, 1 to 5, 1 to 5) as Variant
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…
Dim MyArray(1 to 3) as String MyArray (1) = “Dog” MyArray (2) = “Cat” MyArray (3) = “Chicken”
…or in a two dimensional array.
Dim MyArray (1 to 5, 1 to 10) as Integer MyArray(2,5) = 7
To assign values stored in a worksheet…
Dim MyArray() As Variant
MyArray = Range("A1").CurrentRegion
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.
Range("A12") = MyArray(5, 2)
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.
Dim MyArray() As Variant
MyArray = Range("A1").CurrentRegion
Range("A12") = WorksheetFunction.Average(MyArray)
MsgBox "The average value is " & WorksheetFunction.Average(MyArray)
In this example the sum of values in the second row of the array is calculated
Dim MyArray() As Variant
MyArray = Range("A1").CurrentRegion
With WorksheetFunction
Range("A12") = .Sum(.Index(MyArray, 2, 0))
End With
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.
ReDim Preserve myarray(1 To 5, 1 To 11)
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.
myarray = WorksheetFunction.Transpose(Range("A1").CurrentRegion)
Once we have increased the size of the array we can assign new values.
myarray(1, 11) = 11 myarray(2, 11) = 22 myarray(3, 11) = 33 myarray(4, 11) = 44 myarray(5, 11) = 55
Then reverse transpose the array.
myarray = WorksheetFunction.Transpose(myarray)
The code in full..
Dim myarray() As Variant
'store a transposed version of the worksheet data in myarray
'necessary as when you redimension and preserve you can only
'redimension the last dimension and we want to increase the
'number of rows (which will become columns)
myarray = WorksheetFunction.Transpose(Range("A1").CurrentRegion)
'redimension to increase the size of second dimension
'without preserve values are erased when you redimension
ReDim Preserve myarray(1 To 5, 1 To 11)
'add new values
myarray(1, 11) = 11
myarray(2, 11) = 22
myarray(3, 11) = 33
myarray(4, 11) = 44
myarray(5, 11) = 55
'reverse transpose
myarray = WorksheetFunction.Transpose(myarray)
The Locals window shows the new values have been successfully assigned to the new elements in our array.
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.
Dim myarray() As Variant
myarray = Range("A1").CurrentRegion
Range("G1") = "Starting Index for Dimension 1"
Range("H1") = LBound(myarray, 1)
Range("G2") = "Ending Index for Dimension 1"
Range("H2") = UBound(myarray, 1)
Range("G3") = "Starting Index for Dimension 2"
Range("H3") = LBound(myarray, 2)
Range("G4") = "Ending Index for Dimension 2"
Range("H4") = UBound(myarray, 2)
Columns("G:H").AutoFit
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.
Dim myarray() As Variant
myarray = Range("A1").CurrentRegion
Dim Counter As Integer
'redimension the myarray array to include an extra column
ReDim Preserve _
myarray(LBound(myarray, 1) To UBound(myarray, 1), LBound(myarray, 2) To UBound(myarray, 2) + 1)
'loop through each array row and sum and store values
For Counter = LBound(myarray, 1) To UBound(myarray, 1)
With WorksheetFunction
myarray(Counter, UBound(myarray, 2)) _
= .Sum(.Index(myarray, Counter, 0))
End With
Next Counter
'place sum values in the next available column
Range("A1").Offset(0, UBound(myarray, 2) - 1).Resize(UBound(myarray, 1)) _
= WorksheetFunction.Index(myarray, 0, UBound(myarray, 2))
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…
Transaction = Range("a2").CurrentRegion
Next we loop through each record in the database checking to see if the transaction relates to the Brighton branch.
If Transaction(TransactionCounter, 2) = "Brighton" Then
If it does we keep a tally of the number of Brighton records in a variable called BtnTransactionCounter
BtnTransactionCounter = BtnTransactionCounter + 1
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.
ReDim Preserve BrightonTransactions(1 To 8, 1 To BtnTransactionCounter)
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.
For Counter = 1 To 8 BrightonTransactions(Counter, BtnTransactionCounter) _ = Transaction(TransactionCounter, Counter) Next Counter
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.
Worksheets.Add
Then we add the field headings contained in the Transaction array.
Range("A1:H1") = Transaction
Next spew the data out into the new sheet – transposing it into a vertical table.
Range("A2", Range("A2").Offset(BtnTransactionCounter - 1, 7)) =Application.Transpose(BrightonTransactions)
Finally autofit columns.
Columns.AutoFit
The complete code…
Dim Transaction() As Variant
Dim BrightonTransactions() As Variant
Dim BtnTransactionCounter As Long
Dim TransactionCounter As Long
Dim Counter As Long
'Store entire database in the Transaction array
Transaction = Range("a2").CurrentRegion
'Starting in the second row of the database loop through each transaction
For TransactionCounter = 2 To UBound(Transaction, 1)
'If the second column contains a value equal to Brighton...
If Transaction(TransactionCounter, 2) = "Brighton" Then
'...increase the BtnTransactionCounter by 1
BtnTransactionCounter = BtnTransactionCounter + 1
'Redimension the BrightonTransaction array with each instance _
of a Brighton transaction.
ReDim Preserve _
BrightonTransactions(1 To 8, 1 To BtnTransactionCounter)
'Start a counter to populate the BrightonTransactions array
For Counter = 1 To 8
'The BrightonTransactions array equals the current transaction
BrightonTransactions(Counter, BtnTransactionCounter) _
= Transaction(TransactionCounter, Counter)
Next Counter
End If
Next TransactionCounter
'Add a new sheet
Worksheets.Add
'Add the headings in the first row of the Transactions array
Range("A1:H1") = Transaction
'Transpose the BrightonTransaction array onto the new sheet
Range("A2", Range("A2").Offset(BtnTransactionCounter - 1, 7)) _
= Application.Transpose(BrightonTransactions)
'Autofit columns
Columns.AutoFit