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