Array variables

 2020-06-15    Arrays    0    1

Array variables can be used to store multiple items, usually of the same data type. Instead of declaring many different variables you can declare one single array variable that can store multiple items.
If an array variable is declared as a Variant data type, then the array variable can contain items of mixed data types (e.g. numbers and text).
You can declare an array variable with a fixed size when you write your code, or it can have a dynamic size that is set at runtime.
A variable of datatype Variant can also contain data in an array, e.g. as a result of reading data from multiple cells in a worksheet.
Just to make things a bit more complicated (or flexible), each of the items in an array can also be an array, either of the same size or different sizes.
More information about the Collection object.
More information about the Dictionary object.

Arrays can be declared with up to 60 dimensions, but using more than 3 is not very common.
One- and two-dimensional arrays are the most common array types.
A one-dimensional array can e.g. store a list of numbers.
A two-dimensional array store information as "rows and columns", e.g. data from a worksheet cell range.
By default the dimensions in an array variable has a lower bound = 0 unless you specify something else when declaring the variable.
You can override this default setting for each module by inserting Option Base 1 at the top of the module to set the default lower bound to 1 (0 and 1 are the only valid options).
Even if 0 and 1 are the most common lower bounds for arrays, you can declare a lower bound for an array variable using an integer number you find logical for your solution.

Below you can find som examples on how to declare one-dimensional array variables and populating them with data, the examples assumes that you have not changed the default lower bound setting from 0:

    Dim dblDailySales(1 to 7) As Double ' declares a one-dimensional array variable holding 7 items of the data type Double (using index numbers 1 to 7)
    dblDailySales(1) = 1234.56
    dblDailySales(2) = 2234.65
    dblDailySales(3) = 3334.75
    dblDailySales(4) = 3465.23
    dblDailySales(5) = 2892.81
    dblDailySales(6) = 0
    dblDailySales(7) = 0
    
    Dim dblSum As Double, i As Long
    dblSum = 0
    For i = LBound(dblDailySales) To UBound(dblDailySales)
        dblSum = dblSum + dblDailySales(i) ' loop through all array items and add each value to the total
    Next i
    Debug.Print "The total is: " & dblSum
    
    Dim dblMonthlySales(11) As Double ' declares a one-dimensional array variable holding 12 items of the data type Double (using index numbers 0 to 11)
    dblMonthlySales(0) = 10568.76
    dblMonthlySales(1) = 12851.62
    '...
    dblMonthlySales(11) = 16869.12
    
    dblSum = 0
    For i = LBound(dblMonthlySales) To UBound(dblMonthlySales)
        dblSum = dblSum + dblMonthlySales(i) ' loop through all array items and add each value to the total
    Next i
    Debug.Print "The total is: " & dblSum
    
    Dim strNames(1 to 1000) As String ' declares a one-dimensional array variable holding 1000 items of the data type String (using index numbers 1 to 1000)
    strNames(1) = "John Doe"
    strNames(2) = "Florence Holm"
    '...
    strNames(1000) = "Hans Klink"
    
    Dim dblPlotValues(-100 to 100) As Double ' declares a one-dimensional array variable holding 201 items of the data type Double (using index numbers -100 to 100)
    dblPlotValues(-100) = 123.45
    dblPlotValues(-99) = 124.65
    '...
    dblPlotValues(-1) = 119.30
    dblPlotValues(0) = 122.43
    dblPlotValues(1) = 120.98
    '...
    dblPlotValues(99) = 119.65
    dblPlotValues(100) = 121.75
    
    dblSum = 0
    For i = LBound(dblPlotValues) To UBound(dblPlotValues)
        dblSum = dblSum + dblPlotValues(i) ' loop through all array items and add each value to the total
    Next i
    Debug.Print "The total is: " & dblSum
    
    Dim varItems As Variant ' declare a variant variable that can store an array
    varItems = Array(20, 40, 60, 80, 10, 30, 50, 70, 90) ' assign a one-dimensional array of numeric values to the variant variable (using index numbers 0 to itemscount - 1)
    dblSum = 0
    For i = LBound(varItems) To UBound(varItems)
        dblSum = dblSum + varItems(i) ' loop through all array items and add each value to the total
    Next i
    Debug.Print "The total is: " & dblSum
    
    Dim varWeekdays As Variant ' declare a variant variable that can store an array
    varWeekdays = Split("Mon;Tue;Wed;Thu;Fri;Sat;Sun", ";") ' assign a one-dimensional array of string values to the variant variable (using index numbers 0 to itemscount - 1)

    Dim varTemp As Variant ' declare a variant variable that can store an array
    varTemp = Array(20, 40, 60, 80, 10, 30, 50, 70, 90, "Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun") ' assign a one-dimensional array of mixed values to the variant variable (using index numbers 0 to itemscount - 1)
    
Below you can find som examples on how to declare two-dimensional array variables and populating them with data, the examples assumes that you have not changed the default lower bound setting from 0:

    Dim strNamesAndDepts(1 to 100, 1 to 2) As String ' declares a two-dimensional array variable holding 100x2=200 items of the data type String
    strNamesAndDepts(1, 1) = "John Doe"
    strNamesAndDepts(1, 2) = "Sales"
    strNamesAndDepts(2, 1) = "Florence Holm"
    strNamesAndDepts(2, 2) = "Sales"
    strNamesAndDepts(3, 1) = "Hans Klink"
    strNamesAndDepts(3, 2) = "Admin"
    '...
    strNamesAndDepts(100, 1) = "Peter Clavette"
    strNamesAndDepts(100, 2) = "Marketing"
    
    Dim varEmployees(1 to 100, 1 to 5) As String ' declares a two-dimensional array variable holding 100x5=600 items of the data type Variant
    varEmployees(1, 1) = "John Doe"
    varEmployees(1, 2) = "Sales"
    varEmployees(1, 3) = "john.doe@acme.com"
    varEmployees(1, 4) = "555-4567"
    varEmployees(1, 5) = "111 Main Street"
    
    varEmployees(1, 1) = "Hans Klink"
    varEmployees(1, 2) = "Admin"
    varEmployees(1, 3) = "hans.klink@acme.com"
    varEmployees(1, 4) = "555-9876"
    varEmployees(1, 5) = "22 Main Avenue"
    '...
    
    Dim varArray As Variant ' declare a variant variable that can store an array
    varArray = Worksheets(1).Range("A1:D1000").Value ' assign a two-dimensional array of mixed values from a fixed sized cell range to the variant variable (using index numbers 1 to rows count and 1 to columns count)
    
    Dim r As Long, c As Long
    dblSum = 0
    For r = LBound(varArray, 1) To UBound(varArray, 1)
        For c = LBound(varArray, 2) To UBound(varArray, 2)
            On Error Resume Next ' just in case some cells don't contain a number value
            dblSum = dblSum + varItems(r, c) ' loop through all array items and add each value to the total
            On Error Goto 0
        Next c
    Next r
    Debug.Print "The total is: " & dblSum
Fixed sized arrays can be a good thing, but very often you don't know the necessary array size until runtime.
By using dynamic sized array variables you can determine the necessary array size at runtime and then set the necessary array size like this:

    ' determine the necessary items count and store it in the lngDays varible
    Dim lngDays As Long
    lngDays = Day(DateSerial(Year(Date), Month(Date) + 1, 0)) ' count of days in the current month
    ' when you have the necessary items count you can re-dim the dynamic array variable to the correct size
    Dim dblSalesByDay() As Double ' declare a dynamic array variable, doing this will not work: Dim dblSalesByDay(1 to lngDays) As Double
    ReDim dblSalesByDay(1 to lngDays) ' set the array size, any existing data in the array variable will be lost
    ' now you can do something with the array variable to populate it and do any calculations
    Dim i As Long    
    For i = LBound(dblSalesByDay) To UBound(dblSalesByDay) ' or optionally: For i = 1 to lngDays
        dblSalesByDay(i) = SomeFunctionToRetrieveDailySales(i)
    Next i
    
    Dim varOneEmployee As Variant
    lngEmployees = 56 ' determine the current employee count
    Dim varEmployees() As Variant ' declare a dynamic array variable, doing this will not work: Dim varEmployees(1 to lngEmployees, 1 To 5) As Variant
    ReDim varEmployees(1 To lngEmployees, 1 to 5) ' set the array size, any existing data in the array variable will be lost
    For i = 1 to lngEmployees ' or optionally: For i = LBound(varEmployees, 1) To UBound(varEmployees, 1)
        varOneEmployee = SomeFunctionToRetrieveEmployee(i) ' return data as a one-dimensional array with 5 items for one single employee
        varEmployees(1, 1) = varOneEmployee(1)
        varEmployees(1, 2) = varOneEmployee(2)
        varEmployees(1, 3) = varOneEmployee(3)
        varEmployees(1, 4) = varOneEmployee(4)
        varEmployees(1, 5) = varOneEmployee(5)
    Next i
    
    Dim varArray As Variant ' declare a variant variable that can store an array
    varArray = Worksheets(1).Range("A1").CurrentRegion.Value ' assign a two-dimensional array of mixed values from a variable sized cell range to the variant variable (using index numbers 1 to rows count and 1 to columns count)
    ' note! if the returned range only has one single cell, varArray will not be an array variable, it will contain one single value only
    If IsArray(varArray) Then
        ' data from multiple cells was returned as a two-dimensional array
    Else
        ' data from a single cell was returned as a single non-array variant variable
    End If
When you use the ReDim command, any existing data in the array variable will be lost.
It is possible to change the size of a dynamic array variable and keep any existing content using the ReDim Preserve command.
When using the ReDim Preserve command you can't change the dimension count of an array variable, and you can only change the upper bound for the last dimension, e.g. for a two-dimensional array you can change the columns count but not the rows count: ReDim Preserve varArray(sameasbefore to sameasbefore, sameasbefore to newcount).
The new upper bound can be larger or smaller than the existing upper bound.

    Dim varAllItems() As Variant ' declare a dynamic array variable
    Dim lngCount As Long, varItems As Variant, r As Long, c As Long, i As Long
    lngCount = 0 ' total item count for varAllItems
    i = 0 ' array index for varAllItems
    ' read data from a worksheet
    varItems = Worksheets(1).Range("A1").CurrentRegion.Value ' read values from a worksheet range
    If IsArray(varItems) Then
        r = UBound(varItems, 1) - LBound(varItems, 1) + 1 ' the total row count in a two-dimensional array (regardless of the lower bound)
        c = UBound(varItems, 2) - LBound(varItems, 2) + 1 ' the total column count in a two-dimensional array (regardless of the lower bound)
        'r = UBound(varItems, 1) ' the total row count in a two-dimensional array where the lower bound = 1
        'c = UBound(varItems, 2) ' the total column count in a two-dimensional array where the lower bound = 1
        lngCount = lngCount + r * c
        Redim Preseve varAllItems(1 to lngCount) ' set the array size, keep any existing data in the array variable
        For r = LBound(varItems, 1) To UBound(varItems, 1)
            For c = LBound(varItems, 2) To UBound(varItems, 2)
                i = i + 1
                varAllItems(i) = varItems(r, c)
            Next c
        Next r
    End If
    ' read data from another worksheet
    varItems = Worksheets(2).Range("A1").CurrentRegion.Value ' read values from a worksheet range
    If IsArray(varItems) Then
        r = UBound(varItems, 1) - LBound(varItems, 1) + 1 ' the total row count in a two-dimensional array (regardless of the lower bound)
        c = UBound(varItems, 2) - LBound(varItems, 2) + 1 ' the total column count in a two-dimensional array (regardless of the lower bound)
        'r = UBound(varItems, 1) ' the total row count in a two-dimensional array where the lower bound = 1
        'c = UBound(varItems, 2) ' the total column count in a two-dimensional array where the lower bound = 1
        lngCount = lngCount + r * c
        Redim Preseve varAllItems(1 to lngCount) ' set the array size, keep any existing data in the array variable
        For r = LBound(varItems, 1) To UBound(varItems, 1)
            For c = LBound(varItems, 2) To UBound(varItems, 2)
                i = i + 1
                varAllItems(i) = varItems(r, c)
            Next c
        Next r
    End If
Avoid using the ReDim Preserve command a lot of times, e.g. inside a loop.
If you use the ReDim Preserve command a lot of times it will most likely make your macro painfully slow if the dataset is large and you have to add many items to the dynamic array:

    ' avoid using the ReDim Preserve command multiple times
    Dim varItems As Variant, lngCount As Long, r as long, c As Long, varMyData() As Variant
    varItems = Worksheets(1).Range("A1").CurrentRegion.Value ' read values from a worksheet range
    ' loop through the dataset, resize the array and add items when necessary
    lngCount = 0
    For r = LBound(varItems, 1) to UBound(varItems, 1)
        For c = LBound(varItems, 2) to UBound(varItems, 2)
            If Len(varItems(r, c)) > 0 Then
                lngCount = lngCount + 1 ' count cells with content
                Redim Preserve varMyData(1 to lngCount) ' set the dynamic array size, keep any existing data in the array variable
                varMyData(lngCount) = varItems(r, c)
            End If
        Next c
    Next r
This is probably considerably faster and the recommended approach:
Loop through the dataset once to determine the necessary items count, set the dynamic array variable size, loop through the dataset a second time to populate the array content.

    ' loop through the dataset once to determine the necessary items count, ReDim the array variable
    ' loop through the dataset a second time to populate the array variable
    Dim varItems As Variant, lngCount As Long, r as long, c As Long, varMyData() As Variant
    varItems = Worksheets(1).Range("A1").CurrentRegion.Value ' read values from a worksheet range
    ' loop through the dataset to determine the necessary array size
    lngCount = 0
    For r = LBound(varItems, 1) to UBound(varItems, 1)
        For c = LBound(varItems, 2) to UBound(varItems, 2)
            If Len(varItems(r, c)) > 0 Then
                lngCount = lngCount + 1 ' count cells with content
            End If
        Next c
    Next r
    If lngCount > 0 Then
        ReDim varMyData(1 to lngCount) ' set the dynamic array size
        ' loop through the dataset to populate the array variable
        lngCount = 0
        For r = LBound(varItems, 1) to UBound(varItems, 1)
            For c = LBound(varItems, 2) to UBound(varItems, 2)
                If Len(varItems(r, c)) > 0 Then
                    lngCount = lngCount + 1 ' count cells with content
                    varMyData(lngCount) = varItems(r, c)
                End If
            Nest c
        Next r
    End If


Leave a comment:

Your comment will only be published after it has been moderated and found spam free.
Your e-mail address will only be used to display your Gravatar.