## 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.

Dim var As Variant

ar=Array("A", "B", "C", "D") '1D array

Range("A1").Resize(1, UBound(ar)).Value=ar

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.

ar(0)="A"

ar(1)="B"

ar(2)="C"

ar(3)="D"

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 ExplicitSub VarExample() 'Excel VBA where array starts at 1.

Dim var As Variant

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

Range("A1").Resize(1, UBound(ar)).Value=ar

### 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;

**;**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;

Sub VarExample() 'Excel VBA hard coded array.

var=[{"A","B";"C","D";"E","F"}] '2D array

Range("A5").Resize(UBound(var, 1), UBound(var, 2)) =var

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.

Sub test() 'Excel VBA trapping the bounds of the array.

Dim Ub1 As Long

Dim Ub2 As Long

Dim Lb1 As Long

Dim Lb2 As Long

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

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 Base 1

Sub test3() 'Excel VBA example of Arrays.

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)

Var(n, 1)=ar(j, 1)

Var(n, 2)=ar(j, 2)

Var(n, 3)=ar(j, 3)

Var(n, 4)=ar(j, 4)

[f1].Resize(, Ub2)=[{"Week","Match1","Match2","Match3"}]

[f2].Resize(Ub1, Ub2)=Var

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.

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.