Updated: Aug 9, 2022
Arrays are variables where you can store multiple items into one variable, where items could be of the same type (most common) such as strings, integers, doubles...etc. However, you could also have a variant array that stores variants or undefined variables of any type. Below we will go through how to work with arrays and to read data collection, tables from excel into a vba variable in the different ways possible.
Let's start with some variables to kick this off.
Dim MyArray(1 To 5) As String 'declaration
MyArray(1) = "Please"
MyArray(2) = "Analyze"
MyArray(3) = "The"
MyArray(4) = "Attached"
MyArray(5) = "Thx."
'To display the second item ("Analyze")
MsgBox MyArray(2)
We could also create an array in one line which could be very convenient with the Array syntax,
MyArray = Array("Please", "Analyze", "The", "Attached", "Thx.")
'This will display item "The", which is our 3rd item as the array
starts from 0.
MsgBox MyArray(2)
Arrays normally starts with index 0 unless defined like the first example above (1 to 5), or unless you have changed the so called "Option base" to 1 instead of 0, which is the default. Option base is a way to change the starting index, and it can be done once per module level.
Just type: "Option Base 1" at the very top of the module to change the default setting. If I had done that, then both of my previous examples would have shown "Analyze" in the message box.
Examples with option base 0 - default.
'Contains 11 items.
Dim MyArray(10) As String
'Contains 11 items
Dim MyArray(0 To 10) As String 'exactly the same as above
'Contains 10 items
Dim MyArray(1 To 10) As String
'Contains 6 items
'Dim MyArray(5 To 10) As String
For reading smaller data collections to an array variable we can use a loop to get the data in.
Sub Array_From_Worksheet()
'Create a 2-dimensional array. This will create a 4 by 2 array matrix.
Dim MyArray(1 To 4, 1 To 2) As String
'The below provides the specification for MyArray (4,2) if we had not know the demensions.
Debug.Print UBound(MyArray, 1)
Debug.Print UBound(MyArray, 2)
'Create our looping integers
Dim i As Integer, j As Integer
For i = 1 To 4
For j = 1 To 2
'Since I have a header I am adding +1 for the Cells syntax.
MyArray(i, j) = Cells(i + 1, j).Value
Next j
Next i
'To Display "Bond".
MsgBox myarray(1, 2)
End Sub
When we however work with bigger data collections. We might want to consider not using a loop and start specifying the range to read in directly, as that would run considerably faster than iterating through an entire dataset one by one.
Sub Array_From_WorksheetBig()
'Looping in info take a lot of time.
'If you work with big data, just assign a range.
'1 action, 1 line instead of millions of actions.
StartTime = Timer 'Start of timer
'Create a 2-dimensional array.
Dim MyArray(1 To 10000, 1 To 28) As String
'Create our looping integers
Dim i As Integer, j As Integer
For i = 1 To UBound(MyArray, 1) 'using Ubound syntax instead of specifying size.
For j = 1 To UBound(MyArray, 2)
'Since I no longer have a header I don't have to add +1 for row.
MyArray(i, j) = Cells(i, j).Value
Next j
Next i
Debug.Print Round(Timer - StartTime, 2) 'Timer stops
End Sub
Sub ReadFromWorksheet()
'Key note to highlight is that this will read it as a variant and multidimensional.
'Even if only 1 column then syntax for extracting information will be:
'First row
'(1,1),
'Second row etc..
'(2,1)
StartTime = Timer 'Start of timer
'Dim myarray As Variant
MyArray = Range("A1:AB10000").Value '1 line, 1 operation to read in the data.
Debug.Print Round(Timer - StartTime, 2) 'Timer stops
End Sub
When I compare this time vs loop above this is 20k times faster. Yes this is faster by a factor of 20,000 which is why it is key to consider how you write your VBA scripts and the number of operations that you do.
Learn more about VBA here for all my posts: https://www.pls-fix-thx.com/vba
Learn more about Python here for all my posts: https://www.pls-fix-thx.com/python
If you have found this article or website helpful. Please show your support by visiting the shop below.