How to read a collection to array in vba?

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.

Array Basics

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

Read data from worksheet

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

Read bigger data from worksheet

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

Alternative way to read in bigger data

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.

Pls Fix Thx unisex Hoodie

Buy Now