Declaring an array

You declare an array by adding parentheses after the array name and specifying the number of array elements in the parentheses:

Dim myArray(2)

This creates an array, myArray, that contains three elements:

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

Three elements are included because, by default, the index count starts at 0. If the index count needs to start at 1, use Option Base 1 to force the count to start at 1. To do this, place the Option Base statement in the declarations section at the top of the module:

Option Base 1
Sub MyFirstArray()
Dim myArray(2)

This now forces the array to have only two elements.

You also can create an array independently of the Option Base statement by declaring its lower and upper bounds:

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 statement to declare the lower bound. By declaring Dim myArray(1 to 10), you declare the lower bound, 1, and the upper bound, 10. See “Retrieving data from an array” later in this chapter for information on using LBound and UBound.

Declaring a multidimensional array

The arrays just discussed are considered one-dimensional arrays because only one number designates the location of an element of the array. Such an 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 two-dimensional array contains rows and columns.

To declare another dimension to an array, you add another argument. The following creates an array of 10 rows and 20 columns:

Dim myArray(1 to 10, 1 to 20)

Dim myArray (1 to 4, 1 to 10, 1 to 4)

The following code 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

The following code 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.

UBound(MyArray,2).

Filling an array

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:

Click here to view code image

Dim myArray As Variant 'Variants can hold any type of data, including arrays
'Fill the variant with array data
myArray = Array("Name", "Address", "Phone", "Email")
'Unload the array onto a sheet by placing it in a range of the same size
Worksheets("Sheet2").Range("A1").Resize(LBound(myArray), _
     UBound(myArray)).Value = myArray

.Resize(LBound(myArray) + 1, UBound(myArray)+1)

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 and force 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. This code creates an array that is 16 rows by 2 columns:

Click here to view code image

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

Now that you have the data in an array, what can you do with it and how do you get it out? How to loop through the array and extract data from it is explained in the following section.

Retrieving data from an array

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-3):

Click here to view code image

Sub QuickFillMax()
Dim myArray As Variant
 
myArray = Worksheets("Sheet1").Range("B2:C12").Value
MsgBox "Maximum Integer is: " & WorksheetFunction.Max(myArray)
 
End Sub

Data also can be manipulated before 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:

Click here to view code image

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

The results are placed on the sheet in a new column (see Figure 8-4).

Using arrays to speed up code

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 powerful because they can actually make the code run faster!

In the preceding example, each row was processed as it was placed on the sheet. Imagine doing that 10,000 times, 100,000 times, or more. Each time Excel has to write to the sheet, it slows down. You can minimize writing to the sheet by doing all the processing in memory and then writing the data to the sheet one time.

In the following example, the calculated average is placed in a second array: MyAverage. First, you ReDim it so that it has enough room to hold all the calculated values. (See the next section, “Using dynamic arrays,” for more information.) Then, after looping and filling it, you place the entire array on the sheet. Notice that the range you place it in is resized to fit the entire array. Also, because the array was created in code and is just a single element (row), you have to transpose it so it’s in column form:

Click here to view code image

Sub QuickFillAverageFast()
'Writes the data to the sheet once
'Also more flexible with dynamic range
Dim myArray As Variant, MyAverage As Variant
Dim myCount As Long, LastRow As Long
Dim wksData As Worksheet
Set wksData = Worksheets("EveryOther")
With wksData
     LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
     myArray = .Range("B2:C" & LastRow)
    'resize the array so it is 1 row, but as long as the number of rows in the 'array
     ReDim MyAverage(UBound(myArray))
     For myCount = LBound(myArray) To UBound(myArray)
       MyAverage(myCount) = _
         WorksheetFunction.Average(myArray(myCount, 1), _
           myArray(myCount, 2))
     Next myCount
     .Range("D2").Resize(UBound(MyAverage)).Value = _
         Application.Transpose(MyAverage)
End With
End Sub

This is a basic example of using an array to speed up code. The data is already in an easy-to-read ­format so you can simply place it in the array. But what if the data wasn’t so well formatted, such as a report you need to convert to a data table? “Converting a fixed-width report to a data set” in ­Chapter 13, “Excel power,” shows how to use a class module, collection, and array to reorganize the data.

Using dynamic arrays

You don’t always know how big an array needs to be. 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 that 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, like this:

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 sets the upper bound after it knows how many sheets are in the workbook:

Click here to view code image

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 use it many times, such as in a loop, you lose all the data it holds. To prevent this from happening, use Preserve. The Preserve keyword enables you to resize the last array dimension while keeping existing data. There are limitations to redimming an array: you cannot use it to change the number of dimensions and you can only resize the last dimension.

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:

Click here to view code image

Sub XLFiles()
Dim FName As String
Dim arNames() As String
Dim myCount As Integer
'Dir returns the first filename that matches the argument. To get the rest of
'the filenames, call Dir again, but with no arguments. When no more
'filenames match, Dir returns an empty string ("").
FName = Dir("C:Excel VBA O365 by Jelen & Syrstad*.xls*")
Do Until FName = ""
    myCount = myCount + 1
    ReDim Preserve arNames(1 to 1, 1 To myCount)
    arNames(myCount) = FName
    FName = Dir
Loop
End Sub

Passing an 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:

Click here to view code image

Sub PassAnArray()
Dim myArray() As Variant
Dim myRegion As String
'place the data in the table into the array
myArray = Worksheet("Data").ListObjects("mySalesData").DataBodyRange
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 Long
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

Next steps

Arrays are a type of variable used for holding more than one piece of data. In Chapter 9, “Creating classes and collections,” you discover 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.