Using Arrays in VBA Procedures

After you declare an array, you must assign values to its elements. This is often referred to as "filling an array" or "populating an array." Let's try out a VBA procedure that uses a one-dimensional array to programmatically display a list of six American cities:

1. Open a new workbook and save it as Chap07.xls.

2. Switch to the Microsoft Visual Basic Editor window and rename the VBA project Tables.

3. Insert a new module into the Tables (Chap07.xls) project, and rename this module StaticArrays.

4. Enter the following FavoriteCities procedure:

' start indexing array elements at 1 Option Base 1

Sub FavoriteCities()

'now declare the array Dim cities(6) As String

'assign the values to array elements cities(1) = "Baltimore"

cities(2) = "Atlanta"

citiesp) = "Boston"

cities(4) = "Washington"

cities(6) = "Trenton"

'display the list of cities

MsgBox cities(1) & Chr(13) & cities(2) & Chr(13) & cities(3) & Chr(13) & cities(4) & Chr(13) _ & cities (5) & Chr(13) & cities(6) End Sub

Before the FavoriteCities procedure begins, the default indexing for an array is changed. Notice that the position of the Option Base 1 statement is at the top of the module window before the Sub statement. This statement tells Visual Basic to assign the number 1 instead of the default 0 to the first element of the array.

The array cities() is declared with six elements of String data type. Each element of the array is then assigned a value. The last statement uses the MsgBox function to display the list of cities. When you run this procedure, the city names will appear on separate lines (see Figure 7-1). You can change the order of the displayed data by switching the index values.

Figure 7-1:

You can display the elements of a one-dimensional array with the MsgBox function.

Figure 7-1:

You can display the elements of a one-dimensional array with the MsgBox function.

Run the FavoriteCities procedure and check the results.

Modify the FavoriteCities procedure so that it displays the names of the cities in the reverse order (from 6 to 1).

Tip 7-4: Initial Value of an Array Element

Until a value is assigned to an element of an array, the element has its default value. Numeric variables have a default value of zero (0), and string variables have the default value of the empty string ("").

0 0

Post a comment