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.

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)).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 VarExample() '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 also 1
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) 'Start of Excel Array typical 1
   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) 'Start of Excel Array typical 1

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.