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. 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 list. 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 list or 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. For now, though, let's look at the qualities of the most common, and most easily visualized, array structure, the one-dimensional 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()

That example creates a Variant array. VBA then assigns the appropriate subtype or subtypes when you store data in the array.

But you can specify the data type of the array just as for an ordinary variable. For example, the following statement declares the array named curMonthProfit and assigns 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

See the problem? This array holds 12 items, but in its declaration we 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

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.

Because working with arrays is much easier if you use an Option Base 1 statement, the examples in the rest of this chapter use Option Base 1 statements.

To assign a value to an item in an array, you use the index number to identify the item. 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 String array with three values assigned

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, and 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.

You can declare both fixed-size arrays and dynamic arrays. The examples you've seen so far were fixed-size arrays. For instance, the size of 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 will have been opened by the user when 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()

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.

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.

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

Because an array is a type of variable, you may occasionally need to check whether a particular variable name denotes an array or a scalar variable (an ordinary variable; one that isn't an array). 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"

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)

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 integers in another array into ascending order or descending order. But writing a program that sorts is much more difficult.

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.

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 1, "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/masteringvba2010.

How to Locate Line Numbers in the Editor

In this book, code of any length is given line numbers so the lines can be referenced easily. If you're following along with a code description in this book, you'll sometimes want to know what line the blinking cursor is on in the editor code. Just look at the field at the far right of the editor's Standard toolbar, right next to the blue Help question mark. This field always displays the current line number and character number, as you can see in this screen shot:

Here's what happens in Listing 7.1:

Figure 7.6. When the Sort_an_Array procedure has finished sorting, it displays the sorted list in another message box.

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.

This section shows 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

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

Example 7.2. A simple linear search

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 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.

  

  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.

• 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.

  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.

How to Generate Random Numbers

Sharp-eyed readers will notice that a 0 sometimes appears in the array in the previous example, and what's more, 10 never appears. In other words, the code Int(Rnd * 10) randomly produces numbers ranging from 0 to 9. This is a byproduct of the rounding performed by the Int command. Here's how to use the Rnd command to produce the exact range of numbers you want.

When asking VBA for a random number, you specify the upper limit of the range of numbers you want and then multiply that number by Rnd. For example, if you want to simulate rolling dice, you need random numbers from 1 to 6, so 6 is the upper limit. You multiply the result that Rnd gives you by 6. But then you must add 1 to make the result range from 1 to this upper limit. (Otherwise, the result is a range between 0 and the upper limit, minus 1, as in the code in the previous example, which provided numbers from 0 to 9 rather than 1 to 10.)

The Int function must be used because Rnd provides only fractions. Here are some typical results when the Rnd function executes:

• 0.4542078

• 0.3570231

• 0.1499811

• 0.7043958

• 0.928786

Because these are fractions, you need to multiply to get whole numbers. But the Int command rounds off any fractional part of the final result. So here is how you would get a random number from 1 to 50:

X = Int(Rnd * 50 + 1)

To get a range from 0 to an upper limit, specify as the upper limit a number 1 higher than you actually want. And don't add 1 inside the parentheses. This example provides a random number from 0 to 50:

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 dumb—it starts looking at the beginning of the array and then looks through everything else 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 for 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 narrow the search area.)

The binary search determines the relevant 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. For example, say a binary search is searching 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.

Example 7.3. Searching through a large array

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 users 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 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 2; 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.

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

Figure 7.10. The Binary_Search_ of_Array procedure tells the user whether the search was successful (left) or not.

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/masteringvba2010.

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

   
   

   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 brown dots rather than one in the margin indicator bar, to indicate the breakpoint.)

3. Press the F5 key (or choose Run

   

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:

   Iteration	intTop	intMiddle	intBottom
   0	1000	—	1
   1	499	500	1
   2	249	250	1
   3	124	125	1
   4	124	62	63
   5	93	94	63
   6	77	78	63
   7	69	70	63
   8	69	66	67
   9	69	68	67
   10	67	67	67

At the end of the tenth iteration of the loop, intThousand(intMiddle) is equal to varUserNumber, so the loop ends. Clearly, breakpoints, single-stepping, and the Locals window are excellent debugging tools. Chapter 3, "Debugging Your Code and Handling Errors," explores these and other debugging techniques.