How to work with and modify multidimensional arrays in VBA?


vba multidimensional arrays big data

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.


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

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
VBA read array from worksheet









Read bigger data from worksheet

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
VBA read big data array from worksheet faster

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.


14 views0 comments