Programming Fundamentals Using VBA - Arrays
(Page 8 of 9 )
In many ways, the discussion of arrays is tied closely to variables. An array is a variable that contains multiple values. The number of values that the variable will hold must be decided and declared in advance. You are also going to learn how to reference values inside the array.
As I was writing this, my technical editor asked me, “What about dynamic arrays?” Well, as you will learn, the concept of a dynamic array is a slight falsehood. You still need to declare, in advance, the number of values it will hold. The only difference is that you declare them before the array is used, during the runtime of the program (as opposed to when you are writing the code).
You are also going to learn how to allocate memory properly so that your array does not take up too much room.
Components of an Array Each value in the array is called an element. Since an array variable has multiple elements in it, you need a way to pick them out or reference them individually.
You can do that by using a number called an index. Most of the time, the first element of an array is index number 0.
If you could look behind the scenes of an array of names, using variable name strName, it might look something like this:
strName (0) “John Smith”
(1) “Jane Doe”
(2) “Rosemary Brown”
(3) “Anita LaScala”
(4) “Bob Gray”
Notice that even though the index numbers only go up to 4, this is a five-element array. Again, the first element usually begins at 0. (Note that as we progress through this chapter, we will see some exceptions.)
If you wanted to select Anita LaScala’s name out of the array for printing, you would use
Print strName(3)
Anita is in index position 3. However, just to confuse things a bit, it is the fourth element of the array. This is the source of many a problem in programming and, a little later on in the chapter, we will examine a way of possibly correcting for that.
VBA gives us two flavors of arrays:
- Static array The number of elements in the array, called the length of the array, is decided in advance and remains fixed.
- Dynamic array The length of the array is variable and not decided in advance.
Static Arrays A static array has a predetermined length and does not change. Since this is the simplest of the arrays, we will start here.
Declaring a static array is similar to declaring a variable, with one small exception:
Dim intMyScores(10) As Integer
You have to be careful how you view this. You are probably thinking that we just declared an array of 10 elements. However, what we really did was declare an array of 11 elements, with the first element being 0, and the last element being index number 10 (sometimes called the upper bound). See the difference?
The lower bound, or lowest index number, of this array is 0.
You need to do this in order to properly allocate the memory necessary to hold the array.
If you wanted to, you could declare multiple arrays in a procedure as follows:
Dim strName(6) As String, intMyScores(10) As Integer
By default, the first index value is 0, strings are initialized as empty, and integers are initialized at 0.
Let’s set up an example. In this procedure, you are going to create two For…Next loops. The first one will allow you to populate the array, and the second will print the contents of the array back to you. Here is the code:
Sub arrayTest()
Dim i As Integer
Dim intMyScores(10) As Integer
For i = 0 To 10
intMyScores(i) = InputBox("Enter number " & i, "Static Array Test")
Next
For i = 0 To 10
Debug.Print "For array element " & i & " the number is " & _
intMyScores(i)
Next
End Sub
Programmers like to use the lowercase i as the variable representing the index of the array. It is just a programming convention. Here we are asking it to serve double duty: it is the counter variable of the For…Next loop, and it is also the representation of the array’s index. Notice that you always refer to an array variable by the variable’s name followed by the element number, or index, in parentheses. In the example, we are using the loop variable of i to help us populate our array.
As a nice little extra, notice that I have a concatenation for the prompt in the input box. This will help you keep track of what element you are entering.
Your input box should look something like this:
After you enter the elements, the second For loop takes over and should give you the printout in the Immediate window:
With a static array, you declare the size of the array right in the code. In other words, it is done during design time.
There is one little problem with the preceding code example. You could somehow end up declaring an incorrect lower or upper bound. This could result in a runtime error. VBA helps you out a bit with two built-in functions: LBound(array name)and UBound(array name). This returns the bounds of the array.
You could change the syntax of the For loop in the previous code as follows:
For i = LBound(intMyScores) To UBound(intMyScores)
intMScores(i) = InputBox("Enter number " & i, "Static Array Test")
Next
Next: Dynamic Arrays >>
More Visual Basic.NET Articles
More By McGraw-Hill/Osborne
|
 This article is excerpted from chapter six of the book Access VBA Programming, written by Charles E. Brown and Ron Petusha (McGraw-Hill/Osborne, 2004; ISBN: 0072231971). Check it out at your favorite bookstore today. Buy this book now.
|
|
