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 able to contain more (or fewer) values and how to specify the scope for an array. (It's similar to the techniques for variables we explored in Chapter 6, “Working with Variables, Constants, and Enumerations.”) And you'll learn how to determine, while your macro executes, whether a particular variable name represents an array or a just an ordinary, single-value variable.
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's data as a whole to efficiently work with all the values it contains at once. Or you can also 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). 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 some visual examples of arrays later in this chapter.
The previous description is of the simplest kind of 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.
Because an array is a kind of variable, you declare an array by using the familiar keywords:
, Dim
, Private
, and Public
. 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 Static
: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 (
) 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.Dim
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
and makes it the Currency data type:curMonthProfit
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
, assigns the Currency data type, and specifies that the array contains 12 items:curMonthProfit
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
statement is Dim curMonthProfit(11) As Currency
rather than 11
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 12
, the second is curMonthProfit(0)
, and the twelfth is curMonthProfit(1)
. (Remember that you can avoid this counterintuitive approach by using the curMonthProfit(11)
Option Base
statement.)1
Figure 7.1 shows a simple representation of the single-dimensional array created by the
statement.Dim curMonthProfit(11) As Currency
To make numbering start at 1, add an Option
statement to the declarations area at the beginning of the module in which you declare the array. Here is an example:Base
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
statement.1
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
, and Kong
to the first three items in an array named Taipei
: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.
The
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.curMonthProfit
To declare a multidimensional array, you separate the dimensions with commas. For example, the following statements declare a two-dimensional array named
with three items in each dimension:MyArray
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
or item 1,2
.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.
You can declare both fixed-size arrays and dynamic arrays. The examples you've seen so far were fixed-size arrays. For instance, the
array was specified as having 12 items. You say right up front how big the array is.curMonthProfit
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
and causes VBA to assign it the Variant data type (because no data type is specified):arrTestArray
Dim arrTestArray()
You can change the size of, or redimension, a dynamic array by using the
statement. For example, to redimension the dynamic array ReDim
declared in the previous example and assign it a size of five items, you could use the following statement:arrTestArray
ReDim arrTestArray(5)
When you use
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.ReDim
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
statement instead of a straight Preserve
statement:ReDim
ReDim Preserve arrTestArray(5)
If you use
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.ReDim Preserve
Note that
works only for the last dimension of a multidimensional array. You can't preserve the data in other dimensions in a multidimensional array.ReDim Preserve
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
and displays it in a message box:arrMyArray
Option Base 1
MsgBox arrMyArray(4)
The following statement returns the fifth item in the second dimension of a two-dimensional array named
and displays it in a message box:arrMy2DArray
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
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 Erase
: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 an ordinary variable (sometimes called a Scalar variable). To find out whether a variable is an array, use the
function with the variable's name. For example, the following statements check the variable IsArray
and display the results in a message box:MyVariable
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
function and the LBound
function. UBound
returns the lower bound, the index number of the first item; LBound
returns the upper bound, the index number of the last item.UBound
The
function and the LBound
function have the following syntax:UBound
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—
for the first dimension, 1
for the second, and so on. (If you omit the dimension argument, VBA assumes you mean the first dimension.)2
For example, the following statement returns the upper bound of the second dimension in the array named
and displays it in a message box:arrMyArray
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. Unfortunately, VBA has no built-in command to sort an array.
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 or descending order.
However, 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. (A loop is some code that keeps repeating until a condition is met.) If the second item being bubbled belongs further up the list than the first item, the sorting loop 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 very cheap these days. The bubble sort itself hasn't 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. Remember that a loop repeats doing something 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 shows the code for a bubble sort.
Go ahead and 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. Don't worry, though; things will become clearer as you progress through this book. What's more, you never need to write a bubble sort from scratch anyway—just copy this one, modifying it a little to sort whatever particular 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/masteringvba2016xxxxxxxxxxxxxxxxxx
Here's what happens in Listing 7.1:
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 the procedures in the Code window.Sort_an_Array
procedure. Line 5 is a spacer.strArray
with 12 subscripts (array items).strTemp
. Line 9 declares the String variable strMsg
.X
, Y
, and i
. Line 11 is a spacer.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.
vbCr
characters) to the String variable strMsg
.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.)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
).strMsg
, as shown in Figure 7.5. Line 31 is a spacer.There's an outer loop and an inner loop. The outer For…
loop ends in line 41 with the Next
statement. This loop runs from Next X
(in other words, X = 1) to X = LBound(strArray)
(in other words, X = 11, the upper bound of the array, minus 1).X = (UBound(strArray) - 1)
For…
Next
loop, which runs from Y = (X + 1)
to Y = UBound(strArray)
.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)
, thereby switching the values.strTemp
to an empty string.If
statement.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 searching—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 an array that's already sorted. However, speed, particularly when dealing with the usually small tasks performed by macros, is rarely an issue.
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:
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.Linear_Search_of_Array
procedure. Line 5 is a spacer.intArray
with 10 subscripts.i
(traditionally programmers use the name I
for a loop's counter variable—i for increment or iteration).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
.The procedure declares the variable
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.varUserNumber
For…
Next
loop that repeats 10 times: from i = 1
to 1 = 10
.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.)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.Next i
statement. Line 18 is a spacer.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).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.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.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.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.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.)For…
Next
loop that runs from i = 1
to i = UBound(intArray)
—once for each subscript in the array.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.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.)strMsg
to convey to the user the result of the linear search operation. Figure 7.8 shows the result of a successful search.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:
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.Binary_Search_of_Array
procedure.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).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.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.
intTop
variable the upper bound of the array.intBottom
the lower bound.Do…
Loop
Until
loop that performs the bulk of the binary searching. Here are the details:
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
).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,
has a value of intTop
on the first iteration of the loop, and 1000
has a value of intBottom
, so 0
receives the value intMiddle
(500
divided by 1000
).2
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.If
statement.strMsg
String variable.varUserNumber
to intThousand(intMiddle)
; if it matches.strMsg
a string telling the user where the search item was found in the array.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:
intTop
, intMiddle
, and intBottom
.
Figure 7.11 shows the Locals window while the procedure is running.
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.VBA creates and populates the array and then stops at line 22.
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
is equal to intThousand(intMiddle)
, 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.varUserNumber
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?arrMyArray
.