Arrays and Looping Statements

Several looping statements you learned earlier (For.. .Next and For Each.. .Next) will come in handy now that you're ready to perform such tasks as populating an array or displaying the elements of an array. It's time to combine the skills you've learned so far.

How can you rewrite the FavoriteCities procedure so that it shows the name of each city in a separate message box? The FavoriteCities2 procedure in the following hands-on replaces the last statement of the original procedure with the For Each.. .Next loop.

© Hands-On 7-2: Using Arrays and Loops

1. In the Visual Basic Editor window, insert a new module.

2. Enter the following FavoriteCities2 procedure in the Code window. Option Base 1

Sub FavoriteCities2() ' declare the array Dim cities(6) As String Dim city As Variant

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

cities(2) = "Atlanta"

cities(3) = "Boston"

cities(4) = "Washington"

cities(6) = "Trenton"

' display the list of cities in separate messages For Each city In cities MsgBox city

Next End Sub

3. Choose Run | Run Sub/UserForm to execute the FavoriteCities2 procedure.

Notice that the For Each.. .Next loop uses the variable city of the Variant data type. As you recall from the previous section of this chapter, the For Each. Next loop allows you to loop through all of the objects in a collection or all of the elements of an array and perform the same action on each object or element. When you run the FavoriteCities2 procedure, the loop will execute as many times as there are elements in the array.

In Chapter 4 you practiced passing arguments as variables to subroutines and functions. The procedure CityOperator in the next hands-on demonstrates how you can pass elements of an array to another procedure.

Part I

© Hands-On 7-3: Passing Elements of an Array to Another Procedure

1. In the Visual Basic Editor window, insert a new module.

2. Enter the following two procedures in the module's Code window.

3. Execute the CityOperator procedure (choose Run | Run Sub/UserForm).

Option Base 1

Sub CityOperator()

' declare the array Dim cities(6) As String

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

cities(2) = "Atlanta"

cities(3) = "Boston"

cities(4) = "Washington"

cities(6) = "Trenton"

' call another procedure and pass ' the array as argument Hello cities() End Sub

Sub Hello(cities() As String) Dim counter As Integer

For counter = 1 To 6

MsgBox "Hello, " & cities(counter) & "!" Next End Sub

Notice that the declaration of the Hello procedure includes an array type argument named cities(). Passing array elements from one subroutine to another subroutine or function procedure allows you to reuse the same array in many procedures without unnecessary duplication of the program code.

Passing Arrays between Procedures

When an array is declared in a procedure, it is local to this procedure and unknown to other procedures. However, you can pass the local array to another procedure by using the array's name followed by an empty set of parentheses as an argument in the calling statement. For example, the statement Hello cities() calls the procedure named Hello and passes to it the array cities().

You can also put your newly acquired knowledge about arrays and loops to work in real life. If you're an avid lotto player who is getting tired of picking your own lucky numbers, have Visual Basic do the picking. The Lotto procedure below populates an array with six numbers from 1 to 51.

Introduction to Access 2003 VBA Programming

© Hands-On 7-4: Using Arrays and Loops in Real Life

1. In the Visual Basic Editor window, insert a new module.

2. Enter the following Lotto procedure in the module's Code window.

3. Execute the Lotto procedure (choose Run | Run Sub/UserForm) to get the computer-generated lottery numbers.

Sub Lotto()

Const spins = 6 Const minNum = 1 Const maxNum = 51 Dim t As Integer Dim i As Integer Dim myNumbers As String Dim lucky(spins) As String myNumbers = "" For t = 1 To spins Randomize lucky(t) = Int((maxNum - minNum + 1) * Rnd + minNum)

' check if this number was picked before For i = 1 To (t - 1)

If lucky(t) = lucky(i) Then lucky(t) = Int((maxNum - minNum + 1) * Rnd + minNum) i = 0

End If Next i

MsgBox "Lucky number is " & lucky(t), , "Lucky number " & t myNumbers = myNumbers & " -" & lucky(t) Next t

MsgBox "Lucky numbers are " & myNumbers, , "6 Lucky Numbers" End Sub

The Randomize statement initializes the random number generator. The instruction Int((maxNum - minNum + 1) * Rnd + minNum) uses the Rnd function to generate a random value from the specified minNum to maxNum. The Int function converts the resulting random number to an integer. Instead of assigning constant values for minNum and maxNum, you can use the InputBox function to get these values from the user.

The inner For.. .Next loop ensures that each picked number is unique — it may not be any one of the previously picked numbers. If you omit the inner loop and run this procedure multiple times, you'll likely see some occurrences of duplicate numbers.

' looping variable in outer loop ' looping variable in inner loop ' string to hold all picks ' array to hold generated picks

Part I

0 0

Post a comment