Chapter 7

Using Array Variables

In this chapter, you'll learn how to use arrays—containers that can store multiple values at the same time. An array is a kind of super-variable.

You'll start by examining what arrays are and what you use them for. You'll then examine how to create them, populate them, and erase them. Along the way, you'll look at how to resize an array to make it contain more (or fewer) values, how to specify the scope for an array, and how to find out while your macro executes whether a particular variable name represents an array or a just an ordinary, single-value variable.

In this chapter you will learn to do the following:

• Understand what arrays are and what you use them for
• Create and use arrays
• Redimension an array
• Erase an array
• Find out whether a variable is an array
• Sort an array
• Search an array

What Is an Array?

An array is a variable on steroids—a variable that can contain multiple values (but they must be of the same data type).

You can access the array itself as a whole to work with all the values it contains at once. Or you can access any individual value stored within the array by specifying its index number, which indicates its position within the array.

If you're having difficulty visualizing what this means, try picturing an array as a numbered list, similar to an enumeration (as described in Chapter 6, “Working with Variables, Constants, and Enumerations”). Each item in the list is located in its own row and is identified by an index number, so you can access the value of the item by just specifying its index number. It's like houses on a street: they all share the same name, such as Maple Drive, but each has a distinguishing number all its own. You'll see visual examples of arrays later in this chapter.

The previous description is of a simple array—a numbered list like a row of houses on a street. Such an array is said to have only one dimension. However, later in this chapter you'll see that you can construct more complicated arrays, which are called multidimensional. They're more like a crossword puzzle with both rows and columns.

For now, though, let's look at the qualities of the most common, and most easily visualized, array structure, the one-dimensional array.

Declaring an Array

An array is a kind of variable, so you declare an array by using the familiar keywords: Dim, Private, Public, and Static. To indicate that it's an array, however, you add a pair of parentheses after the array's name. For example, the following statement declares an array named curMonthProfit:

Dim varMonthProfit()

If you had left off the parentheses, then you would have created an ordinary variable capable of holding only a single value:

Dim varMonthProfit

Because no data type was specified in the declaration (Dim) of the preceding array example, this example creates a Variant array. VBA then assigns the appropriate data types (String, Integer, and so on) when you store data in the array.

But you can specify the data type of an array, just as you would for an ordinary variable. For example, the following statement declares the array named curMonthProfit and makes it the Currency data type:

Dim curMonthProfit() As Currency

You can also specify the number of items in the array by using an array subscript. For example, the following statement declares the array named curMonthProfit, assigns the Currency data type, and specifies that the array contains 12 items:

Dim curMonthProfit(11) As Currency

Now you can see one aspect of the zeroth problem. This array holds 12 items, but in its declaration we must specify 11! The array subscript in the Dim curMonthProfit(11) As Currency statement is 11 rather than 12 because by default an array's index starts at 0 rather than 1. That 0 index number gives this list an extra element. The 1st item is curMonthProfit(0), the 2nd is curMonthProfit(1), and the 12th is curMonthProfit(11). (You can avoid this counterintuitive approach by using the Option Base 1 statement.)

Figure 7.1 shows a simple representation of the single-dimensional array created by the Dim curMonthProfit(11) As Currency statement.

FIGURE 7.1 The single-dimensional array created by the statement Dim curMonthProfit(11) As Currency can be thought of as looking like this.

To make numbering start at 1, add an Option Base statement to the declarations area at the beginning of the module in which you declare the array. Here is an example:

Option Base 1 'at the beginning of the code sheet

Dim curMonthProfit(12) As Currency

Figure 7.2 shows a simple representation of how this array would look.

You can also specify both the lower and upper bounds of an array explicitly. This example code states that the lower bound is to be 1 and the upper bound is 12:

Option Base 1 'at the beginning of the code sheet

Dim curMonthProfit(1 To 12) As Currency

Because learning to use arrays is much easier for beginners if we start with an index of 1, the examples in the rest of this chapter use Option Base 1 statements.

FIGURE 7.2 The single-dimensional array created by the statement Dim curMonthProfit(12) As Currency with the Option Base 1 statement. Compare this to Figure 7.1.

Storing Values in an Array

To assign a value to an item in an array, you use each item's index number to identify it. For example, the following statements assign the values London, Hong Kong, and Taipei to the first three items in an array named strLocations:

Option Base 1

Dim strLocations(6) As String

strLocations(1) = "London"
strLocations(2) = "Hong Kong"
strLocations(3) = "Taipei"

Figure 7.3 shows how this array can be envisioned.

FIGURE 7.3 A simple

Multidimensional Arrays

The curMonthProfit example in the previous section is a one-dimensional array, which is the easiest kind of array to use. But VBA supports arrays with up to 60 dimensions—enough to tax the visualization skills of anyone without a PhD in multidimensional modeling. You probably won't want to get this complicated with arrays—two, three, or four dimensions are enough for most purposes. In fact, one dimension is enough for many purposes.

To declare a multidimensional array, you separate the dimensions with commas. For example, the following statements declare a two-dimensional array named MyArray with three items in each dimension:

Option Base 1
Dim MyArray(3, 3)

Figure 7.4 shows how you might represent the resulting array. Note that inside each item in this figure's table you can see the pair of index numbers you would use to access it, such as item 1,2 or item 3,2.

FIGURE 7.4 You can think of a two-dimensional array as consisting of rows and columns.

Multidimensional arrays sound forbidding, but a two-dimensional array is quite straightforward if you think of it basically as a table that consists of rows and columns.

In this example, the first series of three elements appears down the first column of the table, the second series of three elements appears down the second column, and so on.

The information in any series doesn't need to be related to information in the other series, although it does need to be of the same data type. For example, you could assign three folder names to the first dimension of a String variable array (they would be in column 1), the names of your three cats to the second dimension (more strings), a list of the names of the Three Stooges to the third dimension (the third column in the table), and so on. You could then access the information in the array by specifying the position of the item you want to access—for instance, the second item in the first column of the table (item 1,2). You'll learn how to do this in just a minute.

Similarly, you could picture a three-dimensional array as being something like a workbook of spreadsheets—rows and columns, with further rows and columns in the third dimension (down, or away from you).

But that's about the limit of easily pictureable arrays—four-dimensional and larger arrays start to tax the imagination. A row of honeycombs, a set of apartment buildings? It gets difficult.

Declaring a Dynamic Array

You can declare both fixed-size arrays and dynamic arrays. The examples you've seen so far were fixed-size arrays. For instance, the curMonthProfit array was specified as having 12 items.

Dynamic arrays are useful when the number of values you need to store will vary. For example, for a procedure that arranges windows side by side, you might create an array to contain the name of each open window. But while writing the code, you can't know how many windows might want to open while the macro runs. You'll probably want to use a dynamic array to contain the information. That way the array can be sized to fit the situation.

To declare a dynamic array, you use a declaration statement without specifying the number of items (you include the parentheses but leave them empty). For example, the following statement declares a dynamic array named arrTestArray and causes VBA to assign it the Variant data type (because no data type is specified):

Dim arrTestArray()

Redimensioning an Array

You can change the size of, or redimension, a dynamic array by using the ReDim statement. For example, to redimension the dynamic array arrTestArray declared in the previous example and assign it a size of five items, you could use the following statement:

ReDim arrTestArray(5)

When you use ReDim to redimension an array like this, you lose the values currently in the array. If so far you've only declared the array as a dynamic array and it contains nothing, losing its contents won't bother you. There are no contents.

But in other situations an array might be full of data, so you'll want to increase the size of an array while keeping its current contents. To preserve the existing values in an array when you raise its upper bound, use a ReDim Preserve statement instead of a straight ReDim statement:

ReDim Preserve arrTestArray(5)

If you use ReDim Preserve to reduce the size of the array (to lower its upper bound), you of course lose the information stored in any items not included in the redimensioned array. For example, if you have a five-subscript (five-item) array with information in each item and then you redimension it using ReDim Preserve so that it has only three subscripts, you lose the information in the fourth and fifth subscripts.

Note that ReDim Preserve works only for the last dimension of a multidimensional array. You can't preserve the data in other dimensions in a multidimensional array.

Returning Information from an Array

To get information from an array, you use an index number to specify the position of the information you want to return. For example, the following statement returns the fourth item in the array named arrMyArray and displays it in a message box:

Option Base 1


MsgBox arrMyArray(4)

The following statement returns the fifth item in the second dimension of a two-dimensional array named arrMy2DArray and displays it in a message box:

Option Base 1

MsgBox arrMy2DArray(2,5)

To return multiple items from an array, specify each item individually.

Erasing an Array

To erase the contents of an array, use the Erase command with the name of the array. This command reinitializes the items in a fixed-size array and frees the memory taken by items in dynamic arrays (completely erasing the array). For example, the following statement erases the contents of the fixed-size array named arrMyArray:

Erase arrMyArray

Finding Out Whether a Variable Is an Array

Because an array is a type of variable, you may occasionally need to check whether a particular variable name denotes an array or an ordinary variable (sometimes called a scalar variable). To find out whether a variable is an array, use the IsArray function with the variable's name. For example, the following statements check the variable MyVariable and display the results in a message box:

If IsArray(MyVariable) = True Then
    Msg = "MyVariable" & " is an array."
Else
    Msg = "MyVariable" & " is not an array."
End If

MsgBox Msg, vbOKOnly + vbInformation, "Array Check"

Finding the Bounds of an Array

To find the bounds of an array, you use the LBound function and the UBound function. LBound returns the lower bound, the index number of the first item; UBound returns the upper bound, the index number of the last item.

The LBound function and the UBound function have the following syntax:

LBound(array [, dimension])

UBound(array [, dimension])

Here, array is a required argument specifying the name of the array, and dimension is an optional variant specifying the dimension whose bound you want to return—1 for the first dimension, 2 for the second, and so on. (If you omit the dimension argument, VBA assumes you mean the first dimension.)

For example, the following statement returns the upper bound of the second dimension in the array named arrMyArray and displays it in a message box:

MsgBox UBound(arrMyArray, 2)

Sorting an Array

You'll sometimes need to sort an array, especially when you load information into the array from an external source rather than assigning values one by one in your code.

Sorting is easy to understand conceptually: You simply rearrange things into the desired order. For example, you could sort the strings in one array into alphabetical order or reverse alphabetical order, or the numbers in another array into ascending order or descending order. But writing a program that sorts is much more difficult. So, don't write it. Just copy it from examples on the Internet, or from the following example.

This section shows you a simple form of sorting—the bubble sort, so called because the items being sorted to the earlier positions in the array gradually bubble up to the top. The bubble sort consists of two loops that compare two items in the array; if the second item belongs further up the list than the first item, the sort reverses their positions, and the comparisons continue until the whole list is sorted into order. The bubble sort is a relatively inefficient method of sorting items, but it's easy to grasp, and processor cycles are comparatively cheap these days. The bubble sort hasn't itself become any more efficient over the years, but processor speeds have sure ramped up.

This example also introduces you to a major element of programming: the loop. Loops are an important tool found in many procedures and projects. In effect, a loop repeats some action until a condition is met. It's like saying, “Keep rearranging these attendance cards until the stack is alphabetized.” Chapter 12, “Using Loops to Repeat Actions,” shows you how to work with loops.

Listing 7.1 contains the code for the bubble sort.

 1.  Option Explicit
 2.  Option Base 1
 3.
 4.  Sub Sort_an_Array()
 5.
 6.      'declare the array and other variables
 7.      Dim strArray(12) As String
 8.      Dim strTemp As String
 9.      Dim strMsg As String
10.      Dim X As Integer, Y As Integer, i As Integer
11.
12.      'assign strings to the array
13.      strArray(1) = "nihilism"
14.      strArray(2) = "defeatism"
15.      strArray(3) = "hope"
16.      strArray(4) = "gloom"
17.      strArray(5) = "euphoria"
18.      strArray(6) = "despondency"
19.      strArray(7) = "optimism"
20.      strArray(8) = "pessimism"
21.      strArray(9) = "misery"
22.      strArray(10) = "happiness"
23.      strArray(11) = "bliss"
24.      strArray(12) = "mania"
25.
26.      strMsg = "Current items in array:" & vbCr & vbCr
27.      For i = 1 To UBound(strArray)
28.          strMsg = strMsg & i & ":" & vbTab & strArray(i) & vbCr
29.      Next i

30.      MsgBox strMsg, vbOKOnly + vbInformation, "Array Sorting: 1"
31.
32.      For X = LBound(strArray) To (UBound(strArray) − 1)
33.          For Y = (X + 1) To UBound(strArray)
34.              If strArray(X) > strArray(Y) Then
35.                  strTemp = strArray(X)
36.                  strArray(X) = strArray(Y)
37.                  strArray(Y) = strTemp
38.                  strTemp = ""
39.              End If
40.          Next Y
41.      Next X
42.
43.      strMsg = "Items in sorted array:" & vbCr & vbCr
44.      For i = 1 To UBound(strArray)
45.          strMsg = strMsg & i & ":" & vbTab & strArray(i) & vbCr
46.      Next i
47.      MsgBox strMsg, vbOKOnly + vbInformation, "Array Sorting: 2"
48.
49.  End Sub

Read through this code, and the explanation of it that follows, to see how much of it you can understand. At this point, you might not grasp much at all. But don't worry; things will become clearer as you progress through this book. What's more, you need never write a bubble sort from scratch anyway—just copy this one, modifying it a little to sort whatever array you're dealing with. And remember, you can copy all the code in this book from this book's website at www.sybex.com/go/masteringvba2013.

Here's what happens in Listing 7.1:

• Line 1 contains an Option Explicit statement to force explicit declarations of variables, and line 2 contains an Option Base 1 statement to make array index numbers start at 1 rather than 0. These two statements appear in the General Declarations zone of the code sheet, above any other procedure in the Code window. Line 3 is a spacer—a blank line inserted just to make the code easier to read. You can remove it if you wish, or add more spacers—it's your call. VBA ignores blank lines.
• Line 4 begins the Sort_an_Array procedure. Line 5 is a spacer.
• Line 6 is a comment line prefacing the declaration of the array and the variables. Line 7 declares the String array strArray with 12 subscripts (array items). Line 8 declares the String variable strTemp. Line 9 declares the String variable strMsg. Line 10 declares the Integer variables X, Y, and i. Line 11 is a spacer.
• Line 12 is a comment line explaining that the next 12 statements (lines 13 through 24) assign strings to the array. The strings used are words describing various moods. Line 25 is a spacer.
• Lines 26 through 30 build a string out of the strings assigned to the array and then display it in a message box. This section of code is included to help users easily see what's going on if they run the procedure rather than stepping through it. Line 26 assigns introductory text and two carriage returns (two vbCr characters) to the String variable strMsg. Line 27 starts a For… Next loop that runs from i = 1 to i = UBound(strArray)—in other words, once for each item in the array. (The loop could also have run to i = 12 because the upper bound of the array is set, but using the upper bound is more flexible than hard-coding values.) Line 28 adds to strMsg the value of the counter variable i, a colon, a tab (vbTab), the contents of the array item currently referenced (strArray(i)), and a carriage return (vbCr). Line 29 concludes the loop, and line 30 displays a message box containing strMsg, as shown in Figure 7.5. Line 31 is a spacer.

  FIGURE 7.5 The Sort_an_Array procedure displays a message box of the unsorted terms so that the user can see how things start out.

  

• The sorting part of the procedure takes place in lines 32–41. Here are the details:
  • Line 32 begins a set of nested loops: one inside another. There's an outer loop and an inner loop. The outer For… Next loop ends in line 41 with the Next X statement. This loop runs from X = LBound(strArray) (in other words, X = 1) to X = (UBound(strArray) − 1) (in other words, X = 11, the upper bound of the array, minus 1).
  • Line 33 begins the inner (nested) For… Next loop, which runs from Y = (X + 1) to Y = UBound(strArray). Line 40 ends this loop.
  • Line 34 compares strArray(X) to strArray(Y). If strArray(X) is greater than strArray(Y)—in other words, if strArray(X) should appear after strArray(Y) in the alphabetized array—line 35 assigns strArray(X) to strTemp, line 36 assigns strArray(Y) to strArray(X), and line 37 assigns strTemp to strArray (Y), thus switching the values. Line 38 restores strTemp to an empty string. Line 39 ends the If statement. Line 40 ends the inner loop, line 41 ends the outer loop, and line 42 is a spacer.
• Lines 43 through 47 essentially repeat lines 26 through 30, displaying a message box (shown in Figure 7.6) of the now-sorted array so that the user can see that the sort has worked.

  FIGURE 7.6 When the Sort_an_Array procedure has finished sorting, it displays the sorted list in a second message box.

  

• Line 48 is a spacer, and line 49 ends the procedure.

Searching through an Array

Another task you sometimes need to perform with an array is searching to find a particular value in it. This is similar to rifling through a box of recipe cards until you find Ralph's Jailhouse Chili.

The following sections show you two methods of sorting—a linear search, which you can perform on either a sorted array or an unsorted array, and a binary search, which is faster but works only on a sorted array.

Performing a Linear Search through an Array

A linear search is a simple kind of search: You start at the beginning of the array and check each item until you find your target, or until you reach the end of the array and must report not found.

Before executing this code, display the Immediate window in the editor by pressing Ctrl+G or choosing View Immediate Window. This procedure prints information in the Immediate window so that you can see what's going on—and whether the code is running as intended. Using the Immediate Window like this to check output is often preferable to displaying message boxes as we did in the previous section. With the Immediate window, you don't have to click the message boxes closed, and the window can also be scrolled, displaying as much information as you wish.

Listing 7.2 contains the code for a simple linear search through a one-dimensional array.

 1.  Option Explicit
 2.  Option Base 1
 3.
 4.  Sub Linear_Search_of_Array()
 5.
 6.      'declare the array and the variables
 7.      Dim intArray(10) As Integer
 8.      Dim i As Integer
 9.      Dim varUserNumber As Variant
10.      Dim strMsg As String
11.
12.      'add random numbers between 0 and 10 to the array
13.      'and print them to the Immediate window for reference
14.      For i = 1 To 10
15.          intArray(i) = Int(Rnd * 10)
16.          Debug.Print intArray(i)
17.      Next i
18.
19.  Loopback:
20.      varUserNumber = InputBox _
             ("Enter a number between 1 and 10 to search for:", _
             "Linear Search Demonstrator")
21.      If varUserNumber = "" Then End
22.      If Not IsNumeric(varUserNumber) Then GoTo Loopback
23.      If varUserNumber < 1 Or varUserNumber > 10 Then GoTo Loopback
24.
25.      strMsg = "Your value, " & varUserNumber & _
             ", was not found in the array."
26.
27.      For i = 1 To UBound(intArray)
28.          If intArray(i) = varUserNumber Then
29.              strMsg = "Your value, " & varUserNumber & _
                     ", was found at position " & i & " in the array."
30.              Exit For
31.          End If
32.      Next i
33.
34.      MsgBox strMsg, vbOKOnly + vbInformation, "Linear Search Result"
35.
36.  End Sub

Here's what happens in Listing 7.2:

• As in the previous listing, line 1 contains an Option Explicit statement to force explicit declarations of variables, and line 2 contains an Option Base 1 statement to make the index numbers of arrays start at 1 rather than 0. These two statements appear in the declarations part of the code sheet, before any other procedure. Line 3 is a spacer.
• Line 4 begins the Linear_Search_of_Array procedure. Line 5 is a spacer.
• Line 6 is a comment line prefacing the declaration of the array and the other variables that the code uses. Line 7 declares the Integer array intArray with 10 subscripts. Line 8 declares the Integer variable I (traditionally programmers use the name I for a loop's counter variable—I for increment or iteration).
• Line 9 declares the Variant variable varUserNumber, which the code uses to store the user's input from an input box. (More on this control in a moment.) Line 10 declares the String variable strMsg. Line 11 is a spacer.
• The procedure declares the variable varUserNumber as a Variant rather than an Integer. This way, Visual Basic doesn't automatically halt execution and display an error message if the user enters something other than an integer (for example, text) in the input box.
• Lines 12 and 13 contain an extended comment line on the code in lines 14 through 17. (These two lines could be combined into one logical line by adding a continuation character at the end of the first line and omitting the apostrophe at the beginning of the second line, but the code is easier to read when the second line begins with the comment character as well.)
• Line 14 begins a For… Next loop that repeats 10 times: from i = 1 to 1 = 10. Line 15 assigns to the current item in the intArray array the integer result of a random number multiplied by 10: intArray(i) = Int(Rnd * 10). (The Rnd function generates a random number between 0 and 1 with a good number of decimal places. So the procedure multiplies that random number by 10 to get a number between 0 and 10 and then takes the integer portion of the number. In other words, the Int command strips off any fractional result, any values to the right of the decimal point.) Line 16 then uses the Print method of the Debug object to print the current item in intArray to the Immediate window. This is an easy way for you, the programmer, to examine the values generated randomly for the array. The user never sees the Immediate window. Line 17 ends the loop with the Next i statement. Line 18 is a spacer.
• Line 19 contains a label, named Loopback, used to return execution to this point in the code if the user's input does not meet required conditions (If it's not between 1 and 10).
• Line 20 assigns to the Variant variable varUserNumber the result of the user's input. An input box (shown in Figure 7.7) prompts the user to enter a number between 1 and 10.
• Line 21 then compares the contents of varUserNumber to an empty string—the result you get if the user clicks the Cancel button in the input box or clicks the OK button without entering anything in the text box. If varUserNumber is an empty string, the End statement ends execution of the procedure.
• Line 22 uses the IsNumeric function to see whether the contents of varUserNumber are numeric. If they're not, the GoTo Loopback statement returns execution to the Loopback label, after which the input box is displayed again for the user to try their luck once more. Line 23 checks to see if varUserNumber is less than 1 or greater than 10. If either is the case, another GoTo Loopback statement returns execution to the Loopback label, and the input makes another appearance. Line 24 is a spacer.

  FIGURE 7.7 The Linear_Search_of_Array procedure displays an input box prompting the user to enter a number between 1 and 10. The array itself is printed in the Immediate window.

  

  VBA IS FLEXIBLE

  Note the flexibility of VBA here: The code solicits user input and makes sure that it's a number between 1 and 10 (inclusive). Though that number is still stored in a Variant rather than explicitly converted to an Integer, VBA still performs the comparison needed.

• Line 25 assigns to the String variable strMsg a preliminary message stating that the value (which it specifies) was not found in the array. (If the code finds the value in the array, it changes the message before displaying it.) Line 26 is a spacer.
• Lines 27 through 32 contain the searching part of the procedure. Line 27 begins a For… Next loop that runs from i = 1 to i = UBound(intArray)—once for each subscript in the array. Line 28 compares intArray(i) to varUserNumber; if there's a match, line 28 assigns to strMsg a string telling the user at which position in the array the value was found, and line 29 uses an Exit For statement to exit the For… Next loop. (If line 28 does not match, the Next i statement in line 32 causes the code to loop.)
• Line 33 is a spacer. Line 34 displays a message box containing strMsg to convey to the user the result of the linear search operation. Figure 7.8 shows the result of a successful search. Line 35 is a spacer, and line 36 ends the procedure.

  FIGURE 7.8 Line 34 of Listing 7.2 displays a message box telling the user the result of the linear search operation.

  

X = Int(Rnd * 50 + 1)

X = Int(Rnd * 51)

Performing a Binary Search through an Array

As you saw in the previous section, a linear search is easy to perform, but it's pretty simple and slow—it starts looking at the beginning of the array and then checks each element, each item, in turn. This approach works fine for small searches, such as the 10-subscript array you searched in the last example, but you wouldn't want to try it on anything the size of a phone book—even in a small town. For serious, heavy-duty searching, you need a smarter approach.

For conventional purposes, a binary search is a good way to approach searching a sorted array. A binary search formalizes the technique you probably use when searching for something like a lost TV remote control. You expect it to be in a given location—somewhere in the living room, probably near the couch. So you focus your attention on the relevant area and search it thoroughly. (With a linear search, you search everywhere in the house, from start to finish, without any attempt to intelligently narrow the search area.)

The binary search technique (technically called an algorithm) determines the most likely target area by dividing the sorted array in half, establishing which half will contain the search item, and then repeating the divide-and-interrogate procedure until it either finds the search item or reaches the last subdivisible unit of the array without finding it. Remember, this array is presorted, so if the algorithm is looking for the number 12 in a list from 1 to 20, it's likely that the target will be in the second half of the list.

Here's another example. Say that a binary search is looking for the value 789,789 in a million-subscript array that contains the numbers 1 through 1,000,000 in ascending order. It divides the array into two halves, each of which contains a half million subscripts. It establishes whether the search item is in the first half or the second half and then narrows the search to the appropriate half and divides it into new halves. It establishes whether the search item is in the first of these halves or the second and then focuses on that half, dividing it into halves—and so on until it finds the term or has gotten down to a single subscript.

This is a simple example, but a million is still a hefty number. Listing 7.3 makes things even simpler by using an array of a thousand subscripts that contains the numbers 1 through 1000 in order: The first subscript contains the number 1, the second subscript contains the number 2, and so on up to 1000. The example is unrealistic, but it makes it easy to see what's happening in the code.

 1.  Option Explicit
 2.  Option Base 1
 3.
 4.  Sub Binary_Search_of_Array()
 5.
 6.      'declare the array and the variables
 7.      Dim intThousand(1000) As Integer
 8.      Dim i As Integer
 9.      Dim intTop As Integer
10.      Dim intMiddle As Integer
11.      Dim intBottom As Integer
12.      Dim varUserNumber As Variant
13.      Dim strMsg As String
14.
15.      'populate the array with numbers 1 to 1000, in order
16.      For i = 1 To 1000
17.          intThousand(i) = i
18.      Next i
19.
20.      'prompt the user for the search item
21.  Loopback:
22.      varUserNumber = InputBox _
             ("Enter a number between 1 and 1000 to search for:", _
             "Binary Search Demonstrator")
23.      If varUserNumber = "" Then End
24.      If Not IsNumeric(varUserNumber) Then GoTo Loopback
25.
26.      'search for the search item
27.      intTop = UBound(intThousand)
28.      intBottom = LBound(intThousand)
29.
30.      Do
31.          intMiddle = (intTop + intBottom) / 2
32.          If varUserNumber > intThousand(intMiddle) Then
33.             intBottom = intMiddle + 1
34.          Else
35.             intTop = intMiddle - 1
36.          End If
37.      Loop Until (varUserNumber = intThousand(intMiddle)) _
             Or (intBottom > intTop)

38.
39.      'establish whether the search discovered the search item _
          or not and add the appropriate information to strMsg
40.      If varUserNumber = intThousand(intMiddle) Then
41.          strMsg = "The search found the search item, " _
                 & varUserNumber & ", at position " & intMiddle _
                 & " in the array."
42.      Else
43.          strMsg = "The search did not find the search item, " _
                 & varUserNumber & "."
44.      End If
45.
46.      MsgBox strMsg, vbOKOnly & vbInformation, "Binary Search Result"
47.
48.  End Sub

Here's what happens in Listing 7.3:

• Line 1 contains an Option Explicit statement to force explicit declarations of variables, and line 2 contains an Option Base 1 statement to make the numbering of arrays start at 1 rather than 0. These two statements appear in the declarations part of the code sheet, before any procedure.
• Line 3 is a spacer. Line 4 declares the Binary_Search_of_Array procedure, and line 5 is another spacer.
• Line 6 is a comment line prefacing the declaration of the array (the thousand-subscript Integer array intThousand, declared in line 7) and the other variables that the procedure uses: the Integer variables i (line 8), intTop (line 9), intMiddle (line 10), and intBottom (line 11); the Variant variable varUserNumber (line 12); and the String variable strMsg (line 13). Line 14 is yet another spacer.
• Line 15 is a comment line announcing that lines 16 through 18 populate the array with the numbers 1 to 1000 in order. To do so, these lines use a For… Next loop that runs from i = 1 to i = 1000, assigning the current value of i to the subscript in the array referenced by i—in other words, assigning to each subscript the number that corresponds to its position in the array. Line 19 is a spacer.
• Line 20 is a comment line introducing the section of code (lines 21 through 24) that uses an input box (shown in Figure 7.9) to prompt users to enter a number to search for, and checks that they do so. As in the previous listing, this section of code checks to make sure users don't enter an empty string in the input box (line 23) and terminates execution of the procedure if they did. It also uses a label named Loopback (in line 21), to which the code returns if what a user entered in the input box (in line 22) turns out not to be numeric when line 24 checks. Because this time you know which numbers the array will contain, you don't need to check to make sure that users enter a suitable value. If they want to enter a value that doesn't appear in the array, so be it.

  FIGURE 7.9 The Binary_Search_of_Array procedure prompts the user to enter a number between 1 and 1000.

  

• Line 25 is a spacer, and line 26 is a comment that introduces the section of code that searches for the search item the user entered. Line 27 assigns to the intTop variable the upper bound of the array, and line 28 assigns to intBottom the lower bound. Line 29 is a spacer.
• Lines 30 through 37 contain a Do… Loop Until loop that performs the bulk of the binary searching. Here are the details:
  • Line 30 starts the Do… Loop Until loop with the Do keyword, and line 37 ends it with the Loop Until keywords and the condition ((varUserNumber = intThousand(intMiddle)) Or (intBottom > intTop)). You'll look at loops in detail in Chapter 12; for now, all you need to know is that a Do… Loop Until runs once and then evaluates the condition in the Loop Until statement to determine whether it should end or run again. The condition here specifies that the loop continue until either the value of the subscript in the array identified by intMiddle–intThousand(intMiddle)– matches the value in varUserNumber or the value of intBottom is greater than the value of intTop (intBottom > intTop).
  • Line 31 sets the value of the Integer variable intMiddle to the sum of intTop and intBottom divided by 2 : (intTop + IntBottom) / 2. Doing so gives the midpoint for dividing the array. For example, in the thousand-subscript array, intTop has a value of 1000 on the first iteration of the loop, and intBottom has a value of 0, so intMiddle receives the value 500 (1000 divided by 2).
  • Line 32 tests whether varUserNumber is greater than the value stored in the subscript identified by intMiddle—intThousand(intMiddle), the midpoint of the current section of the array. If it is, the search needs to work on the top half of the array, so line 33 resets intBottom to intMiddle + 1. If it's not, the Else statement in line 34 kicks in, and line 35 resets intTop to intMiddle-1 so that the search works on the lower half of the array.
  • Line 36 ends the If statement, and line 37 tests the condition and continues or terminates the loop, as appropriate.
• Line 38 is a spacer. Line 39 contains a two-line comment introducing the code in lines 40 through 44, which establish whether the search found the search item and assign suitable information to the strMsg String variable. Line 40 compares varUserNumber to intThousand(intMiddle); if it matches, line 41 assigns to strMsg a string telling the user where the search item was found in the array. If it doesn't match, line 43 assigns a string telling the user that the search did not find the search item. Line 45 is a spacer, and line 46 displays a message box telling the user the result of the search. Figure 7.10 shows examples—one successful, one otherwise—of the message box.

  FIGURE 7.10 The Binary_Search_of_Array procedure tells the user whether the search was successful (left) or not.

  

• Line 47 is another spacer, and line 48 ends the procedure.

The most complex part of the procedure is what happens in the loop. Download the code from the book's website at www.sybex.com/go/masteringvba2013.

Copy the code, and paste it into the Visual Basic Editor (this code will work in any VBA-enabled application). Then open up the module and follow these steps:

1. Display the Locals window (View Locals Window) so that you can track the values of the variables intTop, intMiddle, and intBottom. Figure 7.11 shows the Locals window while the procedure is running.

   FIGURE 7.11 Use the Locals window to track the values of the intTop, intMiddle, and intBottom variables as the procedure runs.

   

2. Set a breakpoint in the procedure on line 22 by clicking in the margin indicator bar next to the statement that begins varUserNumber = InputBox. (Because the statement is broken onto three lines, the Visual Basic Editor displays three red dots rather than one in the margin indicator bar, to indicate the breakpoint.)
3. Press the F5 key (or choose Run Run Sub/UserForm) to run the code up to the breakpoint. VBA creates and populates the array and then stops at line 22.
4. Press the F8 key to step through the next statements. The first press displays the input box. Enter the value 67 for this example and click the OK button.
5. As the code enters the Do loop and cycles through it, watch the values of the variables intTop, intMiddle, and intBottom in the Locals window. You'll see them change, as shown in the following list:

   

   

At the end of the tenth iteration of the loop, intThousand(intMiddle) is equal to varUserNumber, so the loop ends. As you can see, breakpoints, single-stepping, and the Locals window are excellent debugging tools. Chapter 17, “Debugging Your Code and Handling Errors,” further explores these and other debugging techniques.

The Bottom Line

Understand what arrays are and what you use them for. Arrays play an important role in computer programming. In some ways they resemble a mini-database, and organized data is central to computing. Computers are sometimes called data processors for good reason, and arrays make it easier for you to manipulate variable data.

Master It What is the difference between an array and an ordinary variable?

Create and use arrays. When you create a new array, you declare it and, optionally, specify the number of values it will contain.

Master It There are four keywords that can be used to declare arrays. Name at least three of them.

Redimension an array. If you want to resize an existing dynamic array, you can redimension it.

Master It Redimensioning an array with the ReDim statement causes you to lose any values that are currently in that array. However, you can preserve these values using a special keyword. What is it?

Erase an array. You can erase all the values in a fixed-size array or completely erase a dynamic array.

Master It Write a line of code that erases an array named arrMyArray.

Find out whether a variable is an array. An array is a type of variable, and you may occasionally need to check whether a particular variable name denotes an array or an ordinary scalar variable (a variable that isn't an array).

Master It Which built-in function can you use in VBA to find out whether a variable is an array or an ordinary, single-value variable?

Sort an array. Visual Basic .NET includes array objects with built-in search and sort methods. In VBA, however, you must write a bit of code to search and sort the values in an array.

Master It Name a popular, understandable, but relatively inefficient sorting technique.

Search an array. Searching through an array can be accomplished in two primary ways. If you have a relatively small array, you can use the simpler, but less efficient technique. With large amounts of data, though, it's best to use the more robust approach.

Master It Name two common ways to search an array.