Specifying the Index Range of an Array

As I mentioned earlier, by default the first element of an array is located at index 0. By using the Option Base 1 statement at the top of your module, you can instruct VBA to begin all arrays at index 1. Another way to specify how your array indexes the elements it contains is by using the To clause in the declaration. A good example of this is an array that holds data specific to the day of the week. For example, suppose you need a variable to hold sales data associated with each day of the week. Listing 3.7 demonstrates how you might do this.

Listing 3.7: Array Usage Example

Sub ArrayExample()

Dim acWeeklySales(1 To 7) As Currency Dim n As Integer Dim sDay As String acWeeklySales(1) = 55100.44 acWeeklySales(2) = 43666.43 acWeeklySales(3) = 67004.11 acWeeklySales(4) = 87121.29 acWeeklySales(5) = 76444.94 acWeeklySales(6) = 98443.84 acWeeklySales(7) = 87772.37

sDay = Choose(n, "Mon", "Tue", "Wed", "Thu", _

"Fri", "Sat", "Sun") Debug.Print _

"Sales for " & sDay & " were $" & acWeeklySales(n)

Next End Sub

Executing ArrayExample produces the following output.

Sales for Mon were $55100.44 Sales for Tue were $43666.43 Sales for Wed were $67004.11 Sales for Thu were $87121.29 Sales for Fri were $76444.94 Sales for Sat were $98443.84 Sales for Sun were $87772.37

Listing 3.7 uses a seven-element array in which the index range is 1 to 7. This makes it easy to translate each element to the day of the week to which it belongs. To do this, I used the Choose function, which works similarly to the Choose worksheet function in Excel. Choose takes an index number (n) and returns the nth item found in the list supplied to it.


Listing 3.7 also introduces the line continuation character (_). I used it in this example to break up a few lines so that they could fit within the pages of this book. You may find it helpful to break up long lines of code so that you can read them without have to scroll horizontally. Make sure that there is a space between the line continuation character and the last element of code or else you'll get a compile error.

0 0

Post a comment