This chapter is from the book
This chapter is from the book
This chapter is from the book
Using Array Variables
In VBA, an array is a group of variables of the same data type. Why would you need to use an array? Well, suppose you wanted to store twenty employee names in variables to use in a procedure. One way to do this would be to create 20 variables named, say, employee1, employee2, and so on. However, it's much more efficient to create a single employees array variable that can hold up to 20 names. VBA creates a single variable with 20 different "slots" into which you can add data (such as employee names). Such an array variable is akin to an Excel range that consists of 20 cells in a row or column: the range is a single entity, but it contains 20 slots (cells) into which you can insert data. The major difference is that you almost always use an array variable to hold data of a single type, such as String. When you declare an array variable you specify the data type, as shown here:
Dim employees(19) As String
As you can see, this declaration is very similar to one you would use for a regular variable. The difference is the 19 enclosed in parentheses. The parentheses tell VBA that you're declaring an array, and the number tells VBA how many elements you'll need in the array. Why 19 instead of 20? Well, each element in the array is assigned a subscript, where the first element's subscript is 0, the second is 1, and so on up to, in this case, 19. Therefore, the total number of elements in this array is 20.
You use a subscript to refer to any element simply by enclosing its index number in the parentheses, like so:
employees(0) = "Ponsonby"
By default, the subscripts of VBA arrays start at 0 (this is called the lower bound of the array) and run up to the number you specify in the Dim statement (this is called the upper bound of the array). If you would prefer your array index numbers to start at 1, include the following statement at the top of the module (in other words, before declaring your first array and before your first procedure):
Option Base 1
Note, too, that after resetting the lower bound in this way, if you want to declare an array with the same number of elements, then you need to adjust the upper bound in the Dim statement accordingly. For example, with the lower bound set to 1, if you want to declare an array variable named employees and you want it to hold up to 20 names, then you need to declare it like so:
Dim employees(20) As String
Dynamic Arrays
What do you do if you're not sure how many subscripts you'll need in an array? You could guess at the correct number, but that will almost always leave you with one of the following problems:
- If you guess too low and try to access a subscript higher than the array's upper bound, VBA will generate an error message.
- If you guess too high, VBA will still allocate memory to the unused portions of the array, so you'll waste precious system resources.
To avoid both of these problems, you can declare a dynamic array by leaving the parentheses blank in the Dim statement:
Dim myArray() As Double
Then, when you know the number of elements you need, you can use a ReDim statement to allocate the correct number of subscripts (notice that you don't specify a data type in the ReDim statement):
ReDim myArray(52)
The following is a partial listing of a procedure named PerformCalculations. The procedure declares calcValues as a dynamic array and totalValues as an integer. Later in the procedure, totalValues is set to the result of a function procedure named GetTotalValues. The ReDim statement then uses totalValues to allocate the appropriate number of subscripts to the calcValues array.
Sub PerformCalculations()
Dim calcValues() As Double, totalValues as Integer
.
.
.
totalValues = GetTotalValues()
ReDim calcValues(totalValues)
.
.
.
End Sub
Listing 3.2 presents a more concrete example. (Note that this procedure uses lots of VBA code that you haven't seen yet, so don't be discouraged if you don't fully understand what's happening here.)
Listing 3.2. A Procedure That Stores the Names of the Worksheets in a Dynamic Array
Sub StoreWorksheetNames()
Dim sheetNames() As String
Dim totalSheets As Integer
Dim sheet As Worksheet
Dim i As Integer
Dim strMessage As String
'
' Store the total number of worksheets
' that are in the current workbook
'
totalSheets = ActiveWorkbook.Worksheets.Count
'
' Now redimension the dynamic array
'
ReDim sheetNames(totalSheets)
'
' Loop through the worksheets to store the names in the array
'
For i = 1 To totalSheets
sheetNames(i - 1) = ActiveWorkbook.Worksheets(i).Name
Next 'i
'
' Loop through the array to add the names to a string
'
strMessage = "Here are the worksheet names:" & vbCrLf
For i = 0 To totalSheets - 1
strMessage = strMessage & sheetNames(i) & vbCrLf
Next 'i
'
' Display the worksheet names
'
MsgBox strMessage
End Sub
This procedure begins by declaring sheetNames as a dynamic array. It then uses the totalSheets variable to store the total number of worksheets that are in the current workbook. The procedure then sets the size of the array based on the totalSheets value:
ReDim sheetNames(totalSheets)
The procedure then uses one loop (see Chapter 6, "Controlling Your VBA Code") to store the worksheet names in the array and a second loop to add the worksheet names to the strMessage variable, which is a String value. Finally, the procedure uses the MsgBox function to display the string, as shown in Figure 3.1.
)
Figure 3.1 The results of the dynamic array procedure in Listing 3.2.
Multidimensional Arrays
If you enter a single number between the parentheses in an array's Dim statement, VBA creates a one-dimensional array. But you also can create arrays with two or more dimensions (60 is the maximum). For example, suppose you wanted to store both a first name and a last name in your employee array. To store two sets of data with each element, you would declare a two-dimensional array, like so:
Dim employees(19,1) As String
The subscripts for the second number work like the subscripts you've seen already. In other words, they begin at 0 and run up to the number you specify. So this Dim statement sets up a "table" (or a matrix, as it's usually called) with 20 "rows" (one for each employee) and two "columns" (one for the first name and one for the last name). So if a one-dimensional array is like an Excel range consisting of cells in a single row or column, a multidimensional array is like an Excel range consisting of cells in multiple rows or columns.
Here are two statements that initialize the data for the first employee:
employees(0,0) = "Biff" employees(0,1) = "Ponsonby"