An array is a type of variable that can be used to hold more than one piece of data. For example, if you have to work with the name and address of a client, your first thought might be to assign one variable for the name and another for the address of the client. Instead, consider using an array, which can hold both pieces of information—and not for just one client, but for hundreds.
Declare an array by adding parentheses after the array name. The parentheses contain the number of elements in the array:
Dim myArray (2)
This creates an array, myArray
, that contains three elements. Three elements are included because, by default, the index count starts at 0:
myArray(0) = 10
myArray(1) = 20
myArray(2) = 30
If the index count needs to start on 1, use Option Base 1
. This forces the count to start at 1. To do this, place the Option Base
statement in the declarations section of the module:
Option Base 1
Dim myArray(2)
This now forces the array to have only two elements.
You can also create an array independent of the Option Base
statement by declaring its lower bound:
Dim myArray (1 to 10)
Dim BigArray (100 to 200)
Every array has a lower bound (Lbound
) and an upper bound (Ubound
). When you declare Dim myArray (2)
, you are declaring the upper bound and allowing the option base to declare the lower bound.
By declaring Dim myArray (1 to 10)
, you declare the lower bound, 1, and the upper bound, 10.
The arrays just discussed are considered one-dimensional arrays because only one number designates the location of an element of the array. The array is like a single row of data, but because there can be only one row, you do not have to worry about the row number—only the column number. For example, to retrieve the second element (Option Base 0
), use myArray (1)
.
In some cases, a single dimension is not enough. This is where multidimensional arrays come in. Whereas a one-dimensional array is a single row of data, a multidimensional array contains rows and columns.
Note
Another word for array is matrix, which is what a spreadsheet is. The Cells
object refers to elements of a spreadsheet—and a cell consists of a row and a column. You have been using arrays all along!
To declare another dimension to an array, add another argument. The following creates an array of 10 rows and 20 columns:
Dim myArray (1 to 10, 1 to 20)
This places values in the first two columns of the first row, as shown in Figure 8.1:
myArray (1,1) = 10
myArray (1,2) = 20
This places values in the first two columns of the second row:
myArray (2,1) = 20
myArray (2,2) = 40
And so on. Of course, this is time-consuming and can require many lines of code. Other ways to fill an array are discussed in the next section.
Now that you can declare an array, you need to fill it. One method discussed earlier is to enter a value for each element of the array individually. However, there is a quicker way, as shown in the following sample code and Figure 8.2:
Option Base 1
Sub ColumnHeaders()
Dim myArray As Variant 'Variants can hold any type of data, including arrays
Dim myCount As Integer
' Fill the variant with array data
myArray = Array("Name", "Address", "Phone", "Email")
' Empty the array
With Worksheets("Sheet2")
For myCount = 1 To UBound(myArray)
.Cells(1, myCount).Value = myArray(myCount)
Next myCount
End With
End Sub
Variant
variables can hold any type of information. Create a Variant
-type variable that can be treated like an array. Use the Array
function to shove the data into the variant, forcing the variant to take on the properties of an array. Notice that you don’t declare the size of the array when you fill it as shown in the previous example.
If the information needed in the array is on the sheet already, use the following to fill an array quickly:
Dim myArray As Variant
myArray = Worksheets("Sheet1").Range("B2:C17")
Although these two methods are quick and straightforward, they might not always suit the situation. For example, if you need every other row in the array, use the following code (see Figure 8.3):
Sub EveryOtherRow()
'there are 16 rows of data, but we are only filling every other row
'half the table size, so our array needs only 8 rows
Dim myArray(1 To 8, 1 To 2)
Dim i As Integer, j As Integer, myCount As Integer
'Fill the array with every other row
For i = 1 To 8
For j = 1 To 2
'i*2 directs the program to retrieve every other row
myArray(i, j) = Worksheets("Sheet1").Cells(i * 2, j + 1).Value
Next j
Next i
'Calculate contents of array and transfer results to sheet
For myCount = LBound(myArray) To UBound(myArray)
Worksheets("Sheet1").Cells(myCount * 2, 4) = _
WorksheetFunction.Sum(myArray(myCount, 1), myArray(myCount, 2))
Next myCount
End Sub
LBound
finds the start location, the lower bound, of the array (myArray
). UBound
finds the end location, the upper bound, of the array. The program can then loop through the array and sum the information as it writes it to the sheet. How to empty an array is explained in the following section.
After an array is filled, the data needs to be retrieved. However, before you do that, you can manipulate the data or return information about it such as the maximum integer, as shown in the following code (see Figure 8.4):
Sub QuickFillMax()
Dim myArray As Variant
myArray = Worksheets("Sheet1").Range("B2:C12")
MsgBox "Maximum Integer is: " & WorksheetFunction.Max(myArray)
End Sub
Data can also be manipulated as it is returned to the sheet. In the following example, Lbound
and Ubound
are used with a For
loop to loop through the elements of the array and average each set. The result is placed on the sheet in a new column (see Figure 8.5).
Note
MyCount + 1
is used to place the results back on the sheet because the Lbound
is 1
and the data starts in Row 2.
Sub QuickFillAverage()
Dim myArray As Variant
Dim myCount As Integer
'fill the array
myArray = Worksheets("Sheet1").Range("B2:C12")
'Average the data in the array just as it is placed on the sheet
For myCount = LBound(myArray) To UBound(myArray)
'calculate the average and place the result in column E
Worksheets("Sheet1").Cells(myCount + 1, 5).Value = _
WorksheetFunction.Average(myArray(myCount, 1), myArray(myCount, 2))
Next myCount
End Sub
So far you have learned that arrays can make it easier to manipulate data and get information from it—but is that all they are good for? No, arrays are so powerful because they can actually make the code run faster!
Typically, when there are columns of data to average such as in the preceding example, your first thought might be the following:
Sub SlowAverage()
Dim myCount As Integer, LastRow As Integer
LastRow = Worksheets("Sheet1").Cells(Worksheets("Sheet1").Rows.Count, 1). _
End(xlUp).Row
For myCount = 2 To LastRow
With Worksheets("Sheet1")
.Cells(myCount, 6).Value = _
WorksheetFunction.Average(Cells(myCount, 2), Cells(myCount, 3))
End With
Next myCount
End Sub
Although this works fine, the program has to look at each row of the sheet individually, get the data, do the calculation, and then place the result in the correct column. Wouldn’t it be easier to grab all the data at one time, and then do the calculations and place the result back on the sheet? Also, with the slower version of the code, you need to know which columns on the sheet to manipulate, which in this example are Columns 2 and 3. With an array, you need to know only what element of the array you want to manipulate.
To make arrays even more useful, instead of using an address range to fill the array, you can use a named range. With a named range in an array, it does not matter where on the sheet the range is.
For example, instead of
myArray = Range("B2:C12")
use this:
myArray = Range("myData")
With the slow method, you need to know where myData
is so that you can return the correct columns. However, with an array all you need to know is that you want the first and second columns.
Note
You can make your array even faster! Technically, if you place a column of data into an array, it is a two-dimensional array. If you want to process it, you must process the row and column.
However, you can process the column more quickly if it is just a single row, as long as it does not exceed 16,384 columns. To do this, use the Transpose
function to turn the one column into one row (see Figure 8.6):
Sub TransposeArray()
Dim myArray As Variant
'place myTran, a single column of data, into array
myArray = WorksheetFunction.Transpose(Range("myTran"))
'return the 5th element of the array
MsgBox "The 5th element of the Transposed Array is: " & myArray(5)
End Sub
You cannot always know how big of an array you will need. You could create an array based on how big it could ever need to be, but that’s a waste of memory—and what if it turns out it needs to be even bigger? To avoid this problem, you can use a dynamic array.
A dynamic array is an array that does not have a set size. In other words, you declare the array but leave the parentheses empty:
Dim myArray ()
Later, as the program needs to use the array, Redim
is used to set the size of the array. The following program, which returns the names of all the sheets in the workbook, first creates a boundless array, and then it sets the upper bound after it knows how many sheets are in the workbook:
Option Base 1
Sub MySheets()
Dim myArray() As String
Dim myCount As Integer, NumShts As Integer
NumShts = ActiveWorkbook.Worksheets.Count
' Size the array
ReDim myArray(1 To NumShts)
For myCount = 1 To NumShts
myArray(myCount) = ActiveWorkbook.Sheets(myCount).Name
Next myCount
End Sub
Using Redim
reinitializes the array. Therefore, if you were to use it many times such as in a loop, you would lose all the data it holds. To prevent this from happening, you need to use Preserve
. The Preserve
keyword enables you to resize the last array dimension, but you cannot use it to change the number of dimensions.
The following example looks for all the Excel files in a directory and puts the results in an array. Because you do not know how many files there will be until you actually look at them, you can’t size the array before the program is run:
Sub XLFiles()
Dim FName As String
Dim arNames() As String
Dim myCount As Integer
FName = Dir("C:Excel VBA 2013 by Jelen & Syrstad*.xls*")
Do Until FName = ""
myCount = myCount + 1
ReDim Preserve arNames(1 To myCount)
arNames(myCount) = FName
FName = Dir
Loop
End Sub
Note
Using Preserve
with large amounts of data in a loop can slow down the program. If possible, use code to figure out the maximum size of the array.
Just like strings, integers, and other variables, arrays can be passed into other procedures. This makes for more efficient and easier-to-read code. The following sub, PassAnArray
, passes the array, myArray
, into the function RegionSales
. The data in the array is summed for the specified region and the result is returned to the sub. Refer to Chapter 14, “User-Defined Functions,” to learn more about using functions.
Sub PassAnArray()
Dim myArray() As Variant
Dim myRegion As String
myArray = Range("mySalesData") 'named range containing all the data
myRegion = InputBox("Enter Region - Central, East, West")
MsgBox myRegion & " Sales are: " & Format(RegionSales(myArray, _
myRegion), "$#,#00.00")
End Sub
Function RegionSales(ByRef BigArray As Variant, sRegion As String) As Long
Dim myCount As Integer
RegionSales = 0
For myCount = LBound(BigArray) To UBound(BigArray)
'The regions are listed in column 1 of the data, hence the 1st column of the array
If BigArray(myCount, 1) = sRegion Then
'The data to sum is the 6th column in the data
RegionSales = BigArray(myCount, 6) + RegionSales
End If
Next myCount
End Function
Arrays are a type of variable used for holding more than one piece of data. In Chapter 9, “Creating Classes, Records, and Collections,” you’ll learn about the powerful technique of setting up your own Class module. With this technique, you can set up your own object with its own methods and properties.