Populating array variables

 2020-06-16    Arrays    0    668

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.
Note: When you populate a variant variable with data from multiple cells from a worksheet it will contain a two-dimensional array with lower bounds = 1.

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:
Sub SetArraySizeAtRuntimeWithReDim()
    ' determine the necessary items count and store it in the lngDays varible
    Dim lngDays As Long, lngEmployees 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
        ' do something
    Else
        ' data from a single cell was returned as a single non-array variant variable
        ' do something
    End If
End Sub


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.
Sub SetArraySizeAtRuntimeWithReDimPreserve()
    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 Preserve 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 Preserve 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
    If lngCount = 0 Then Exit Sub ' no items found
    
    ' do something with varAllItems
End Sub


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:
Sub AvoidUsingMultipleReDimPreserve()
    ' avoid using the ReDim Preserve command multiple times
    ' this will most likely make your macro painfully slow if the dataset is large and you have to add many items to the dynamic array
    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 varItems(r, c) > 0 Then
                lngCount = lngCount + 1 ' count cells with value > 0
                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
    If lngCount = 0 Then Exit Sub ' no matching items was found
    
    ' do something with varMyData
End Sub


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.
Looping through the dataset twice usually don't take very long time if the items are stored in an array.
Sub AvoidUsingMultipleReDimPreserve_ReDimOnce()
    ' 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 varItems(r, c) > 0 Then
                lngCount = lngCount + 1 ' count cells with value > 0
            End If
        Next c
    Next r
    If lngCount = 0 Then Exit Sub ' no matching items was found
    
    ReDim varMyData(1 To lngCount) ' set the dynamic array size
    ' loop through the dataset to populate varMyData
    lngCount = 0
    For r = LBound(varItems, 1) To UBound(varItems, 1)
        For c = LBound(varItems, 2) To UBound(varItems, 2)
            If varItems(r, c) > 0 Then
                lngCount = lngCount + 1 ' count cells with value > 0
                varMyData(lngCount) = varItems(r, c)
            End If
        Nest c
    Next r

    ' do something with varMyData
End Sub


If you want to avoid looping through the data set twice you can add items to a collection and create the result array from the collection content.
Sub AvoidUsingMultipleReDimPreserve_UseCollectionSingleItems()
    ' loop through the dataset once to add items to a collection
    ' loop through the collection and populate the array variable
    Dim coll As Collection, varItems As Variant, r As Long, c As Long
    Set coll = New Collection
    varItems = Worksheets(1).Range("A1").CurrentRegion.Value ' read values from a worksheet range
    ' loop through the dataset to extract the items you want added to the collection
    For r = LBound(varItems, 1) To UBound(varItems, 1)
        For c = LBound(varItems, 2) To UBound(varItems, 2)
            If varItems(r, c) > 0 Then ' desired filtering criteria matches (customize this line)
                coll.Add varItems(r, c) ' add item to the collection
            End If
        Next c
    Next r
    If coll.Count < 1 Then Exit Sub ' no matching items was found
    
    Dim varMyData() As Variant, varItem As Variant
    ReDim varMyData(1 To coll.Count) ' set the dynamic array size
    ' loop through the collection and populate varMyData
    r = 0
    For Each varItem In coll
        r = r + 1
        varMyData(r) = varItem
    Next varItem
    ' do something with varMyData
End Sub


Sub AvoidUsingMultipleReDimPreserve_UseCollectionArrayItems()
    ' loop through the dataset once to add items to a collection
    ' loop through the collection and populate the array variable
    Dim coll As Collection, varItems As Variant, r As Long, c As Long, varRow() As Variant
    Set coll = New Collection
    varItems = Worksheets(1).Range("A1").CurrentRegion.Value ' read values from a worksheet range
    ReDim varRow(LBound(varItems, 2) To UBound(varItems, 2)) ' resize to the same column count as varItems
    
    ' add the first row in varItems as a header row for the result
    For c = LBound(varItems, 2) To UBound(varItems, 2)
        varRow(c) = varItems(r, c)
    Next c
    coll.Add varRow ' add array item to the collection
    
    ' loop through the dataset to extract the items you want added to the collection
    For r = LBound(varItems, 1) + 1 To UBound(varItems, 1) ' skip the header row
        If varItems(r, 4) > 0 Then ' desired filtering criteria matches (customize this line)
            For c = LBound(varItems, 2) To UBound(varItems, 2)
                varRow(c) = varItems(r, c)
            Next c
            coll.Add varRow ' add array item to the collection
        End If
    Next r
    If coll.Count < 2 Then Exit Sub ' no matching items was found
    
    Dim varMyData() As Variant, varItem As Variant
    ReDim varMyData(1 To coll.Count, LBound(varItems, 2) To UBound(varItems, 2)) ' set the dynamic array size
    ' loop through the collection and populate varMyData
    r = 0
    For Each varItem In coll
        r = r + 1
        For c = LBound(varItems, 2) To UBound(varItems, 2)
            varMyData(r, c) = varItem(c)
        Next c
    Next varItem
    ' do something with varMyData
End Sub