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.
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
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() 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.
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.
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…
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.
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.
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