Populating array variables
2020-06-16 Arrays 0 699
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