The Excel VBA Array

An Excel VBA Array is a string or numeric cluster of data in a single row (a one dimensional array) or a multiple column, multiple row (2 dimensional array).  These data repositories are very useful for storing and manipulating data in Excel as when they are used correctly they can be very efficient (the code runs extremely quickly).  The following is an example of a 1D VBA array hard coded.

The following YouTube video goes through the process in a little more detail as it outlines both hard coding data and referring to ranges directly, then outputting those ranges into Excel. The Excel file for the video can be found at the following link:

Excel Arrays.xlsm

 
 
Option Explicit
Sub VarExample() 'A basic VBA array.
Dim ar As Variant
Dim var As Variant

ar = Array("A", "B", "C", "D") '1D array
Range("A1").Resize(1, UBound(ar) + 1).Value=ar
End Sub

When written like the above a 1 D array will always start with 0.  The look of the 1D array in VBA is as follows;

ar=Variant(0 to 3)

ar(0)
ar(1)
ar(2)
ar(3)

Showing the above in a different way the 1D array could be displayed in the following VBA coding.

Sub VarExample2()
Dim ar(0 To 3) As String 'The brackets "()" indicate an array is being dimensioned.

ar(0)="A"
ar(1)="B"
ar(2)="C"
ar(3)="D"
End Sub

There are a couple of exceptions to the 1D array starting with 0. You could use Option Base 1 at the top of your code module (usually just under the Option Explicit statement). Or you could use the square brackets . For example, the following;

ar=[{"A","B","C","D"}] '1D array

will ensure the array starts with 1, so the array will look like this ar(1 to 4). The following is an example;

Option Explicit
Sub VarExample() 'Excel VBA where array starts at 1.
Dim ar As Variant
Dim var As Variant

ar=[{"A","B","C","D"}] '1D array
Range("A1").Resize(1, UBound(ar)).Value=ar
End Sub

The 2 D VBA Array

The way I think of 2 Dimensional Arrays is like the grid of an Excel spreadsheet. The Excel grid in a 2 Dimensional Array goes from left to right.  So an Array which traps the range from A1:B2 would look like the following;

ar=Range("A1:B2")

Which is;

Ar(1,1)=A1
Ar(1,2)=B1
Ar(2,1)= A2
Ar(2,2)=B2

or another way to look at it;

ar=(A1,B1;A2,B2)

where the semi colon ; is a break between the first line of the array and the second line of the array. To add an additional line to the VBA array you could do the following;


Option Explicit
Sub VarExample3() 'Excel VBA hard coded array.
Dim var As Variant

var=[{"A","B";"C","D";"E","F"}] '2D array
Range("A5").Resize(UBound(var, 1), UBound(var, 2)) =var
End Sub

The above variable in Excel speak is 2 columns wide and 3 rows deep.

var=Variant(1 to 3, 1 to 2)

Var(1)
var(1, 1)
var(1, 2)

Var(2)
var(2, 1)
var(2, 2)

Var(3)
var(3, 1)
var(3, 2)

When working with Arrays it is most useful to be able to trap the dimensions of the Array.  Let's expand the range in our example and trap the dimensions within it.

Option Explicit
Sub test() 'Excel VBA trapping the bounds of the array.
Dim arr As Variant
Dim Ub1 As Long
Dim Ub2 As Long
Dim Lb1 As Long
Dim Lb2 As Long

arr=Range("A1:D14")
Ub1=UBound(arr, 1) 'Length of the Excel Array
Ub2=UBound(arr, 2) 'The Width of the Excel Array

Lb1=LBound(arr, 1) 'Start of Excel Array typical 1
Lb2=LBound(arr, 2) 'Width of the Excel Array,4 as D is the Fourth Column

End Sub

In the VBE window if you choose View  - Locals Window, now you can step through the above by pressing F8. You will see that the length of Ub1=14 and the width of the Array Ub2 =4. 

These are important numbers to know when it comes time to output the array into a file or to output the Array into another Array.

Outputting a 2 Dimensional Array into a One Dimensional Array

Option Explicit

Sub test2() 'Excel VBA to output 2 dimensionalArray to one D array.
Dim arr As Variant
Dim Var()
Dim Ub1 As Long
Dim Ub2 As Long
Dim Lb1 As Long
Dim Lb2 As Long

arr=Range("A1:D14")
Ub1=UBound(arr, 1) 'Length of the Excel Array
Ub2=UBound(arr, 2) 'The width of the Excel Array (columns wide)
ReDim Var(Ub1, Ub2)

Var=arr

End Sub

From the above, Var now exactly replicates the data in the Variable arr. If we take this one step further we can isolate data which meets criteria. In the following procedure only the items in Column A which contain the number 1 will be added to the array (var).

Option Explicit
Option Base 1

Sub test3() 'Excel VBA example of Arrays.
Dim arr As Variant
Dim Var()
Dim Ub1 As Long
Dim Ub2 As Long
Dim i As Integer
Dim j As Integer
Dim n As Integer

ar=Range("A1:D14")
Ub1=UBound(ar, 1) 'Length of the Excel Array
Ub2=UBound(ar, 2) 'Width of the Excel Array

ReDim Var(Ub1, Ub2)

For j=2 To Ub1 'Loop through the Array UB1.
If ar(j, 1)=1 Then
n=n + 1
Var(n, 1)=ar(j, 1)
Var(n, 2)=ar(j, 2)
Var(n, 3)=ar(j, 3)
Var(n, 4)=ar(j, 4)
End If
Next j
[f1].Resize(, Ub2)=[{"Week","Match1","Match2","Match3"}]
[f2].Resize(Ub1, Ub2)=Var
End Sub

While the following will transpose a 2 dimensional array to a single range. It is a bit more complex as it does not require a loop to achieve the task. It will transpose the data quite quickly as a result but you would not notice the difference with a data set of this size.

Sub ArraytoRange() 'Excel 2D array transposed to 1D array, then exported to fixed Excel range.
Dim Arr()
Dim ar As Variant ReDim Arr(1, 2)

Arr(0, 0) = "A"
Arr(0, 1) = "B"
Arr(0, 2) = "C"
Arr(1, 0) = "D"
Arr(1, 1) = "E"
Arr(1, 2) = "F"

ar = Split(Join(Application.Index(Arr, 1, 0), vbCrLf) & vbCrLf & Join(Application.Index(Arr, 2, 0), vbCrLf), vbCrLf)
Range("A1:F1") = ar
End Sub

The Arr(1,2) is the size of the array, while the split join statement takes the 2 dimensional array and turns it into a 1 dimensional array.

The following Excel file attached to show the workings and VBA code from above.  Press F8 and run through each line of the VBA code to gain a greater understanding of how to push data from one array to another based on criteria.