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. Why three? Because, by default, the index count starts at zero:

myArray(0) = 10
myArray(1) = 20
myArray(2) = 30

If the index count needs to start on one, use Option Base 1. This forces the count to start at one. The Option Base statement is placed 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.

Multidimensional Arrays

The arrays just discussed are considered one-dimensional arrays—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 don’t 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 isn’t enough. This is where multidimensional arrays come in. Where 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’ve 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 19.1:

myArray (1,1) = 10
myArray (1,2) = 20

Figure 19.1. The VB Editor Watches window shows the first “row” of the array being filled from the previous lines of code.

This places values in first two columns of the second row:

myArray (2,1) = 20
myArray (2,2) = 40

And so on. Of course, this is very time-consuming and could take a lot of lines of code. There are other ways to fill an array, which are discussed in the next section.

Now that you can declare an array, you need to fill it. One method was shown earlier— individually entering a value for each element of the array. There’s a quicker way, as shown in the following sample code and Figure 19.2:

Option Base 1

Sub ColumnHeaders()
Dim myArray As Variant
Dim myCount As Integer

' Fill the array
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

Figure 19.2. Use an array to quickly create column headers.

Remember that Variant variables can hold any type of information. To quickstack the array, create a Variant-type variable that can be treated like an array. When the data is shoved into the variant, it is forced to take on the properties of an array.

But what if the information needed in the array is on the sheet already? Use the following to quickly fill an array.

Dim myArray As Variant

myArray = Worksheets("Sheet1").Range("B2:C17")

Although these two methods are quick and easy, they may not always suit the situation. What if you need every other row in the array? The code to do this follows (see Figure 19.3):

Sub EveryOtherRow()
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
        myArray(i, j) = Worksheets("Sheet1").Cells(i * 2, j + 1).Value
    Next j
Next i

'Empty the array
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

Figure 19.3. Fill the array with only the data needed.

After an array is filled, the data needs to be retrieved. Before you do that, however, you can manipulate it or return information about it, such as the maximum integer, as shown in the following code (see Figure 19.4):

Sub QuickFillMax()
Dim myArray As Variant

myArray = Worksheets("Sheet1").Range("B2:C17")
MsgBox "Maximum Integer is: " & WorksheetFunction.Max(myArray)

End Sub

Figure 19.4. Return the Max variable in an array.

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 then placed on the sheet in a new column (see Figure 19.5).

Figure 19.5. Calculations can be done on the data as it is returned to the sheet.

Sub QuickFillAverage()
Dim myArray As Variant
Dim myCount As Integer
'fill the array
myArray = Worksheets("Sheet1").Range("B2:C17")

'Average the data in the array just as it is placed on the sheet
For myCount = LBound(myArray) To UBound(myArray)
    Worksheets("Sheet1").Cells(myCount + 1, 5).Value = _
    WorksheetFunction.Average(myArray(myCount, 1), myArray(myCount, 2))
Next myCount

End Sub

Okay, so arrays can make it easier to manipulate data and get information from it—but is that all they’re good for? No, arrays are so powerful because they can actually make the code run faster!

Typically, if there are columns of data to average, as in the preceding example, your first thought might be for 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 place it in the correct column. Wouldn’t it be easier to grab all the data at one time, and then do the calculations and place it back on the sheet? Also, with the slower version of the code, you need to know which columns on the sheet to manipulate (Columns 2 and 3 in our example). With an array, you need to know only what element of the array you want to manipulate.

To make this even more useful, rather than use an address range to fill the array, you could use a named range. With a named range in an array, it really doesn’t matter where on the sheet the range is.

Instead of

myArray = Range("B2:C17")

use this:

myArray = Range("myData")

Whereas with the slow method you need to know where myData is so that you can return the correct columns, with an array all you need to know is that you want the first and second columns.

Tip

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.

You can process the column more easily if it is just a single row, as long as it doesn’t exceed 16,384 columns. Use the Transpose function to turn the one column into one row (see Figure 19.6).

Sub TransposeArray()
Dim myArray As Variant

   myArray = WorksheetFunction.Transpose(Range("myTran"))

   'return the 5th element of the array
   MsgBox "The 5th element of the Array is: " & myArray(5)
End Sub

Figure 19.6. Use the Transpose function to turn a two-dimensional array into a one-dimensional array.

You can’t 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 not only a waste of memory. What if it turns out it needs to be bigger? In this case, you can use a dynamic array.

A dynamic array is an array that does not have a set size. Declare the array, but leave the parentheses empty:

Dim myArray ()

Later, as the program needs to use the array, use Redim to set the size of the array. The following program, which returns the names of all the sheets in the workbook, creates a boundless array, but then 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; so if you were to use it many times, such as in a loop, you would lose all the data it holds. Use Preserve to prevent that from happening.

The following example looks for all the Excel files in a directory and puts the results in an array. Because we don’t know how many files there will be until we look at them, we 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:Contracting FilesExcel VBA 2007 by Jelen & Syrstad*.xls*")
Do Until FName = ""
    myCount = myCount + 1
    ReDim Preserve arNames(1 To myCount)
    arNames(myCount) = FName
    FName = Dir
Loop

End Sub

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 returned to the sub.

Sub PassAnArray()
Dim myArray() As Variant
Dim myRegion As String

myArray = Range("mySalesData")
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)
    If BigArray(myCount, 1) = sRegion Then
        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. Chapter 20, “Text File Processing,” covers importing from a text file and writing to a text file. Being able to write to a text file is useful when you need to write out data for another system to read, or even when you need to produce HTML files.