Skip to content

Excel VBA – Storing Data in Arrays & Dynamic Arrays

    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…

    Data

    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.

    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.

    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…

    Results

    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