Declaring an Array

Because an array is a kind of variable, you declare an array by using the familiar keywords: Dim, Private, Public, and Static. However, to indicate that it's an array rather than a normal variable, you add a pair of parentheses after the array's name. For example, the following statement declares an array named varMonthProfit:

Dim varMonthProfit()

If you had left off the parentheses, 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.

However, 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 an 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 first item is curMonthProfit(0), the second is curMonthProfit(1), and the twelfth is curMonthProfit(11). (Remember that 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.

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 include the Option Base 1 statement.

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.

Multidimensional Arrays

The curMonthProfit example in the previous section is a one-dimensional array, which is the easiest kind of array to use. 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 most 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 this two-dimensional 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.

Multidimensional arrays sound forbidding, but a two-dimensional array is quite straightforward if you think of it basically as a table (rather than a list) 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 then in your code would 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 pictured arrays—four-dimensional and larger arrays start to tax the imagination. A row of two-sided 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. You say right up front how big the array is.

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. However, while writing the code, you can't know in advance how many windows might be open while the macro runs. You'll probably want to use a dynamic array to contain this 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 is as yet no data in the array.

But in other situations an array might be full of data, so you'll want to increase the size of an array while retaining 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 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

Determining 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 that has the 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)

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

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

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. Therefore, 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 in this case).
• 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.

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

Binary Searching 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 list of all the phone numbers in the USA. 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 imitates the approach 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 there. (With a linear search, by contrast, you search absolutely everywhere in the house, from front door to back door, without any attempt to intelligently narrow the search area by starting near the TV room.)

The binary search technique (technique in this context is technically called an algorithm) determines the most likely target area by dividing the sorted array in half. Then it posits which half will contain the search item, and then repeats the divide-and-interrogate procedure until it either finds the search item or reaches the last subdivisible unit of the array without finding it. And reports not found.

Remember, this array is presorted, so if the algorithm is looking for the number 12 in a list from 1 to 20, the target must 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.

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

• Line 25 is a spacer.
• 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.
• 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 type loop 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.
  • 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 assigns 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.
• 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.

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/masteringvba2016xxxxxxxxxxxxxx

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.

   

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:

   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, the code 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.