VBA Arrays

An array is a type of variable that can hold multiple values within it. This can be useful where it is necessary to store a number of associated items. For example, if you had a list of names, say Fred, Simon, Freida and Fiona, without an array they would need to be stored in separate variables as follows.

Dim name1 As String
Dim name2 As String
Dim name3 As String
Dim name4 As String

name1 = "Fred"
name2 = "Simon"
name3 = "Freida"
name4 = "Fiona"

There are a number of disadvantages to storing the names in this way. Firstly, if you wanted to add another name to the list then you would have to declare another variable. Secondly, if you wanted to do something programmatically with each name, such as add some text prior to the name to signify that it is a first name, you would have to reference each variable.

name1 = "First Name: " & name1  ' name1 now contains "First Name: Fred"
name2 = "First Name: " & name2  ' name2 now contains "First Name: Simon"
name3 = "First Name: " & name3  ' name3 now contains "First Name: Freida"
name4 = "First Name: " & name4  ' name4 now contains "First Name: Fiona"

This process can be simplified by the use of an array because all of the names can be stored in one place and to programmatically alter each name, a ‘for’ loop, can be used. Adding an extra name would be as easy as adding an extra value to the array. It is also easier to output the values in the array to a spreadsheet, for example.

The following example declares an array called ‘names’ and populates it with the same names as in the above example. A ‘for’ loop is then used to cycle through the array, incorporating the same text with each name as before and then displaying the resulting text in column ‘A’ of a spreadsheet.

Dim names As Variant

names = [{"Fred", "Simon", "Freida", "Fiona"}]

For i = LBound(names) To UBound(names)

   names(i) = "First Name: " & names(i)
   Range("A" & i).Value = names(i)

Next i

Each item in an array has a corresponding index value, which is used to reference that item. In VBA there are a number of different ways of initialising an array. Depending on what method is used, the index will either start at zero or one. In the case of the above example, the index starts at one. The functions ‘LBound’ and ‘UBound’ are used to determine the first and last index values in the array, and therefore the number of times that the statements inside the loop are executed. The variable ‘i’ is used to access the individual items in the array to incorporate the text, along with the name. Finally, the ‘Range’ function is used to specify the cell on the spreadsheet that the text needs to be displayed in. The above will produce the following output in column ‘A’ of a spreadsheet.

First Name: Fred
First Name: Simon
First Name: Freida
First Name: Fiona

If for some reason only one of the array values is needed then a loop is not necessary, for example, the first item in the array could be incorporated into a sentence as follows.

Range("A6").Value = "The first item in the array is " & names(1)

This adds a sentence of text to cell ‘A6’ of a spreadsheet, incorporating the name ‘Fred’.

The first item in the array is First Name: Fred

As well as using the index to select a single value from an array, it can also be used to update a value. Here, the name ‘Fred’ at position one of the array is updated to ‘Frederick’

names(1) = "Frederick"

In order to add a value onto the end of an array, it first needs to be resized to accommodate the extra value, then it can be added.

ReDim Preserve names(1 To UBound(names) + 1)
names(UBound(names)) = "Tommy"

Here the ‘UBound’ function is used to find the current last index value and then one is added to it for the new value. The ‘Preserve’ keyword ensures that the current contents of the array is retained.

Multidimensional Arrays

Multidimensional arrays are useful where you have more than one piece of information, so, to continue with the above example, instead of just storing first names in the array, the last names could also be stored.

Dim names As Variant

names = [{"Fred", "Bloggs"; "Simon", "Smith"; "Freida", "Bloggs"; "Fiona", "Jones"}]

Notice that each first name and last name pair is separated by a semi-colon. In order to display the information in an Excel spreadsheet in a similar fashion as above, nested ‘for’ loops need to be used.

For i = LBound(names) To UBound(names)

  For j = LBound(names, 2) To UBound(names, 2)
    
     Cells(i, j).Value = names(i, j)
    
  Next j

Next i

The outer ‘for’ loop cycles through the sets of values, in this case, the first name and last name pairs, whilst the inner loop cycles through the values within each set. Relating this to a spreadsheet, the outer loop determines the row, whilst the inner loop determines the column. This example uses a different method to reference a cell in a spreadsheet from that which has been used previously. The ‘Cells’ function is used instead of ‘Range’, which refers to a column by a numerical value, rather than a letter. The ‘i’ variable from the outer ‘for’ loop is used to determine the row, whilst the ‘j’ variable from the inner ‘for’ loop is used to determine the column. These two variables also determine what value from the array is assigned to a cell. In this case, first names are assigned to cells in column ‘A’, or ‘1’, its numeric representation, whilst last names are assigned to cells in column ‘B’.

As well as being able to populate a spreadsheet with values from an array, it is also possible to populate an array from the values in a spreadsheet.

Dim names As Variant
Dim rng As Range
Set rng = Range("A1:B4")

names = rng.Value

Here, there is an array called ‘names’, as well as a range variable called ‘rng’, which is initialised with a range of ‘A1:B4’, meaning that it includes cells ‘A1’ to ‘A4’ as well as ‘B1’ to ‘B4’. The values in the range are then assigned to the array.