Home

Counting and Looping

Fundamentals of Loops

Introduction

A loop is a technique used to repeat an action. The Visual Basic language provides various techniques and keywords to perform such actions called loops.

For Each Item in a Collection

In our lesson on introducing collections, we saw that the Visual Basic language provides the For Each...Next statement used to visit each item. That operation is one of the techniques to perform a loop. The formula of the For Each ... Next loop is:

For Each element In collection
    statements
    options
Next

We saw that you must first declare a variable for the element.

Practical LearningPractical Learning: Introducing Loops

  1. Start Microsoft Access
  2. In the list of files, click Exercise3 from the previous lesson
  3. On the Ribbon, click Create and click Form Design
  4. In Controls section of the Ribbon, click the Button and click the form.
    If a wizard starts, click Cancel
  5. On the form, right-click the button and click Build Event
  6. In the Choose Builder dialog box, double-click Code Builder and implement the event as follows:
    Private Sub Command0_Click()
        Dim month
        Dim months As Collection
    
        Set months = New Collection
        
        months.Add "January"
        months.Add "February"
        months.Add "March"
        months.Add "April"
        months.Add "May"
        months.Add "June"
        months.Add "July"
        months.Add "August"
        months.Add "September"
        months.Add "October"
        months.Add "November"
        months.Add "December"
    
        For Each month In months
            MsgBox month
        Next
        
        Set months = Nothing
    End Sub
  7. Return to Microsoft Access and switch the form to Form View
  8. Click the button
  9. Click OK on each message box
  10. Close the form
  11. When asked whether you want to save, click No

For the Next Item in the Collection

If you want to explicitly indicate that you want to access the next item in the collection, after the Next keyword, add the element. The formula of the loop becomes:

For Each element In collection
    statements
    options
Next [ element ]

Here is an example:

Private Sub Command0_Click()
    Dim month
    Dim months As Collection

    Set months = New Collection
    
    months.Add "January"
    months.Add "February"
    months.Add "March"
    months.Add "April"
    months.Add "May"
    months.Add "June"
    months.Add "July"
    months.Add "August"
    months.Add "September"
    months.Add "October"
    months.Add "November"
    months.Add "December"

    For Each month In months
        MsgBox month
    Next month
    
    Set months = Nothing
End Sub

Fundamentals of Loop Counters

Introduction

You can visit each item of a collection based on the index of that item. To assist you with this, the Visual Basic language provides a loop created with For...To...Next. The formula to follow is:

For counter = start To end
  statement(s)
Next

You must use a variable by which the counting would proceed. First declare an Integer (or compatible, such as Byte or Long) variable. That variable will be used in the counter placeholder. In that place, that is, after the For keyword, you must initialize the variable with the start point. Here is an example:

Dim i As Integer

. . .

For i = 1 To . . .

Next

After the To keyword, specify the last value of the counting as the end point. If you know that value, that is, if it is a constant, use it. In the body of the loop, you access each item using the name of the collection and the counter value.

Practical LearningPractical Learning: Introducing Loop Counters

  1. On the Ribbon, click File and click Open
  2. In the list of files, click Business Mathematics from Lesson 16
  3. In the Navigation Pane, right-click Depreciation Schedule and click Design View
  4. On the form, right-click the Generate button and click Build Event
  5. On the Choose Builder dialog box, click Code Builder and click OK
  6. Implement the event as follows:
    Private Sub cmdGenerate_Click()
        Dim cost As Double
        Dim period As Integer
        Dim strSchedule As String
        Dim depreciation As Double
        Dim salvageValue As Double
        Dim estimatedLife As Integer
        Dim depreciations As New Collection
        
        cost = CDbl(Nz(txtCost))
        salvageValue = CDbl(Nz(txtSalvageValue))
        estimatedLife = CDbl(Nz(txtEstimatedLife))
    
        For period = 1 To estimatedLife
            depreciation = SYD(cost, salvageValue, estimatedLife, period)
            depreciations.Add (depreciation)
        Next
    
        strSchedule = "Depreciation Schedule" & vbCrLf & _
                      "===================" & vbCrLf & _
                      "Year" & vbTab & "Depreciation" & vbCrLf & _
                      "===================" & vbCrLf
    
        For period = 1 To estimatedLife
            strSchedule = strSchedule & period & vbTab & FormatNumber(depreciations(period)) & vbCrLf & _
                          "--------------------------------------" & vbCrLf
        Next
    
        If cost > 0 Then
            MsgBox strSchedule, vbOKOnly Or vbInformation, "Straight-Line Method"
        End If
        
        Set depreciations = Nothing
    End Sub
  7. Return to Microsoft Access and switch the form to Form View
  8. Click Machine Value and type 6500
  9. Click Salvage Value and type 2000
  10. Click Estimated Life and type 5
  11. Click the Calculate button:

    The For...To...Next Loop

  12. Click OK on the message box
  13. Save and close the form
  14. On the Ribbon, click File and click Open
  15. In the list of files, click Exercise3 used earlier
  16. On the Ribbon, click Create and click Form Design
  17. In Controls section of the Ribbon, click the Button and click the form.
    If a wizard starts, click Cancel
  18. On the form, right-click the button and click Build Event
  19. In the Choose Builder dialog box, double-click Code Builder
  20. If the values of the loop are coming from a collection, you can use its Count member as the last value of the loop. To see an example, implement the event as follows:
    Private Sub Command0_Click()
        Dim i As Integer
        Dim list As String
        Dim zones As Collection
    
        Set zones = New Collection
    
        list = "United States Times Zones" & vbCrLf & _
               "=-=-=-=-=-=-=-=-=-=-=-=-=-=-=" & vbCrLf
               
        zones.Add ("Pacific Standard Time (PST)")
        zones.Add ("Mountain Standard Time (MST)")
        zones.Add ("Central Standard Time (CST)")
        zones.Add ("Eastern Standard Time (EST)")
        zones.Add ("Atlantic Standard Time (AST)")
        zones.Add ("Alaskan Standard Time (AKST)")
        zones.Add ("Hawaii-Aleutian Standard Time (HST)")
        zones.Add ("Samoa standard time (UTC-11)")
        zones.Add ("Chamorro Standard Time (UTC+10)")
    
        For i = 1 To zones.Count
            list = list & zones(i) & vbCrLf
        Next
    
        MsgBox list
        
        Set zones = Nothing
    End Sub
  21. Return to Microsoft Access and switch the form to Form View
  22. Click the button

    Counting and Looping

  23. Click OK
  24. Close the form
  25. When asked whether you want to save, click No
  26. On the Ribbon, click File and click Open
  27. In the list of files, click Altair Realtors1 from Lesson 7
  28. On the Ribbon, click Create and click Class Module
  29. Type:
    Enum HouseTypes
        SingleFamily
        Townhouse
        Condominium
        Unknown
    End Enum
    
    Public PropertyNumber As Long
    Public PropertyType As HouseTypes
    Public City As String
    Public Bedrooms As Integer
    Public Bathrooms As Single
    Public MarketValue As Double
  30. In the Project window, click (Name) and type Property
  31. Return to Microsoft Access
  32. On the Ribbon, click Create and click Form Design
  33. In Controls section of the Ribbon, click the Button and click the form.
    If a wizard starts, click Cancel
  34. On the form, right-click the button and click Build Event
  35. In the Choose Builder dialog box, double-click Code Builder
  36. If the items of a collection are object types, you can also use a loop to locate each. To see an example, implement the event as follows:
    Private Sub Command0_Click()
        Dim house As Property
        Dim index As Integer
        Dim strListing As String
        Dim residence As Property
        Dim properties As New Collection
        
        strListing = "Altair Realtors - Properties Listing" & vbCrLf & _
                     "======================================" & vbCrLf & _
                     "Prop #" & vbTab & "Location" & vbTab & vbTab & _
                     "Beds" & vbTab & "Baths" & vbTab & "Market Value" & vbCrLf & _
                     "======================================" & vbCrLf
        
        Set house = New Property
        house.PropertyNumber = 247472
        house.PropertyType = HouseTypes.SingleFamily
        house.City = "Silver Spring"
        house.Bedrooms = 5
        house.Bathrooms = 3.5
        house.MarketValue = 685755
        properties.Add house
        
        Set house = New Property
        house.PropertyNumber = 502840
        house.PropertyType = HouseTypes.Condominium
        house.City = "Washington"
        house.Bedrooms = 2
        house.Bathrooms = 2#
        house.MarketValue = 318950
        properties.Add house
        
        Set house = New Property
        house.PropertyNumber = 150281
        house.PropertyType = HouseTypes.SingleFamily
        house.City = "Bowie State"
        house.Bedrooms = 5
        house.Bathrooms = 3.5
        house.MarketValue = 782575
        properties.Add house
        
        Set house = New Property
        house.PropertyNumber = 240875
        house.PropertyType = HouseTypes.Townhouse
        house.City = "Charleston"
        house.Bedrooms = 3
        house.Bathrooms = 2.5
        house.MarketValue = 348500
        properties.Add house
        
        Set house = New Property
        house.PropertyNumber = 475974
        house.PropertyType = HouseTypes.SingleFamily
        house.City = "Gaithersburg"
        house.Bedrooms = 4
        house.Bathrooms = 2.5
        house.MarketValue = 635775
        properties.Add house
    
        For index = 1 To properties.Count
            Set residence = properties(index)
    
            strListing = strListing & residence.PropertyNumber & vbTab & _
                         residence.City & vbTab & residence.Bedrooms & vbTab & _
                         residence.Bathrooms & vbTab & residence.MarketValue & vbCrLf
        Next
        
        strListing = strListing & "======================================"
        
        MsgBox strListing, vbOKOnly Or vbInformation, "Altair Realtors"
        
        Set house = Nothing
        Set properties = Nothing
    End Sub
  37. Return to Microsoft Access and switch the form to Form View
  38. Click the button:

    The For...To...Next Loop

  39. Click OK on the message box
  40. Return to Microsoft Visual Basic
  41. In the above example, we used a local variable to hold the object from the loop. This is not required. To access the objects another way, change the code as follows:
    Private Sub Command0_Click()
        Dim house As Property
        Dim index As Integer
        Dim strListing As String
        Dim properties As New Collection
        
        strListing = "Altair Realtors - Properties Listing" & vbCrLf & _
                     "======================================" & vbCrLf & _
                     "Prop #" & vbTab & "Location" & vbTab & vbTab & _
                     "Beds" & vbTab & "Baths" & vbTab & "Market Value" & vbCrLf & _
                     "======================================" & vbCrLf
        
        Set house = New Property
        house.PropertyNumber = 247472
        house.PropertyType = HouseTypes.SingleFamily
        house.City = "Silver Spring"
        house.Bedrooms = 5
        house.Bathrooms = 3.5
        house.MarketValue = 685755
        properties.Add house
        
        Set house = New Property
        house.PropertyNumber = 502840
        house.PropertyType = HouseTypes.Condominium
        house.City = "Washington"
        house.Bedrooms = 2
        house.Bathrooms = 2#
        house.MarketValue = 318950
        properties.Add house
        
        Set house = New Property
        house.PropertyNumber = 150281
        house.PropertyType = HouseTypes.SingleFamily
        house.City = "Bowie State"
        house.Bedrooms = 5
        house.Bathrooms = 3.5
        house.MarketValue = 782575
        properties.Add house
        
        Set house = New Property
        house.PropertyNumber = 240875
        house.PropertyType = HouseTypes.Townhouse
        house.City = "Charleston"
        house.Bedrooms = 3
        house.Bathrooms = 2.5
        house.MarketValue = 348500
        properties.Add house
        
        Set house = New Property
        house.PropertyNumber = 475974
        house.PropertyType = HouseTypes.SingleFamily
        house.City = "Gaithersburg"
        house.Bedrooms = 4
        house.Bathrooms = 2.5
        house.MarketValue = 635775
        properties.Add house
    
        For index = 1 To properties.Count
            strListing = strListing & _
            	     properties(index).PropertyNumber & vbTab & _
                         properties(index).City & vbTab & _
                         properties(index).Bedrooms & vbTab & _
                         properties(index).Bathrooms & vbTab & _
                         properties(index).MarketValue & vbCrLf
        Next
        
        strListing = strListing & "======================================"
        
        MsgBox strListing, vbOKOnly Or vbInformation, "Altair Realtors"
        
        Set house = Nothing
        Set properties = Nothing
    End Sub
  42. Close the form
  43. When asked whether you want to save, click No

Options on Loops Counters

The Explicit Next Item

As seen with the For Each loop, if you want to explicitly indicate that you are accessing the next item for each subsequent loop in a counter, type the index variable after the Next keywork. Here are examples:

Private Sub cmdGenerate_Click()
    Dim cost As Double
    Dim period As Integer
    Dim strSchedule As String
    Dim depreciation As Double
    Dim salvageValue As Double
    Dim estimatedLife As Integer
    Dim depreciations As New Collection
    
    cost = CDbl(Nz(txtCost))
    salvageValue = CDbl(Nz(txtSalvageValue))
    estimatedLife = CDbl(Nz(txtEstimatedLife))

    For period = 1 To estimatedLife
        depreciation = SYD(cost, salvageValue, estimatedLife, period)
        depreciations.Add (depreciation)
    Next

    strSchedule = "Depreciation Schedule" & vbCrLf & _
                  "===================" & vbCrLf & _
                  "Year" & vbTab & "Depreciation" & vbCrLf & _
                  "===================" & vbCrLf

    For period = 1 To estimatedLife
        strSchedule = strSchedule & period & vbTab & FormatNumber(depreciations(period)) & vbCrLf & _
                      "--------------------------------------" & vbCrLf
    Next period

    If cost > 0 Then
        MsgBox strSchedule, vbOKOnly Or vbInformation, "Straight-Line Method"
    End If
    
    Set depreciations = Nothing
End Sub

Stepping the Counting Loop

The regular formula of a loop counter increments the counting by 1 at the end of each statement. If you want to control how the incrementing processes, you can set your own, using the Step option. The formula to follow is:

For counter = start To end Step increment
  statement(s)
Next

You can set the incrementing value to your choice. If the value of increment is positive, the counter will be added to its value.

If you want to loop from a higher value to the a lower value, set a negative value to the increment factor and use the following formula:

For counter = end To start Step decrement
  statement(s)
Next

Practical LearningPractical Learning: Stepping the Counting Loop

  1. On the Ribbon, click File and click Open
  2. In the list of files, click Exercise3 used earlier in this lesson
  3. On the Ribbon, click Create and click Form Design
  4. In Controls section of the Ribbon, click the Button and click the form.
    If a wizard starts, click Cancel
  5. On the form, right-click the button and click Build Event
  6. In the Choose Builder dialog box, double-click Code Builder and implement the event as follows:
    Private Sub Command0_Click()
        Dim counter As Integer
        Dim states As New Collection
        
        states.Add ("Alaska"): states.Add ("Alabama"): states.Add ("Arkansas")
        states.Add ("Arizona"): states.Add ("California"): states.Add ("Colorado")
        states.Add ("Connecticut"): states.Add ("Delaware"): states.Add ("Florida")
        states.Add ("Georgia"): states.Add ("Hawaii"): states.Add ("Iowa")
        states.Add ("Idaho"): states.Add ("Illinois"): states.Add ("Indiana")
        states.Add ("Kansas"):  states.Add ("Kentucky"): states.Add ("Louisiana")
        states.Add ("Massachusetts"): states.Add ("Maryland"): states.Add ("Maine")
        states.Add ("Michigan"): states.Add ("Minnesota"): states.Add ("Missouri")
        states.Add ("Mississippi"): states.Add ("Montana")
        states.Add ("North Carolina"): states.Add ("North Dakota")
        states.Add ("Nebraska"): states.Add ("New Hampshire")
        states.Add ("New Jersey"): states.Add ("New Mexico"): states.Add ("Nevada")
        states.Add ("New York"): states.Add ("Ohio"): states.Add ("Oklahoma")
        states.Add ("Oregon"): states.Add ("Pennsylvania")
        states.Add ("Rhode Island"): states.Add ("South Carolina")
        states.Add ("South Dakota"): states.Add ("Tennessee"): states.Add ("Texas")
        states.Add ("Utah"): states.Add ("Virginia"): states.Add ("Vermont")
        states.Add ("Washington"): states.Add ("Wisconsin")
        states.Add ("West Virginia"): states.Add ("Wyoming")
        
        For counter = 1 To states.Count Step 5
            MsgBox states.item(counter)
        Next counter
      
        Set states = Nothing
    End Sub
  7. Return to Microsoft Access and switch the form to Form View
  8. Click the button
  9. Click OK on each message box
  10. Return to Microsoft Visual Basic
  11. To loop backward, change the code as follows:
    Private Sub Command0_Click()
        Dim counter As Integer
        Dim states As New Collection
        
        states.Add ("Alaska"): states.Add ("Alabama"): states.Add ("Arkansas")
        states.Add ("Arizona"): states.Add ("California"): states.Add ("Colorado")
        states.Add ("Connecticut"): states.Add ("Delaware"): states.Add ("Florida")
        states.Add ("Georgia"): states.Add ("Hawaii"): states.Add ("Iowa")
        states.Add ("Idaho"): states.Add ("Illinois"): states.Add ("Indiana")
        states.Add ("Kansas"):  states.Add ("Kentucky"): states.Add ("Louisiana")
        states.Add ("Massachusetts"): states.Add ("Maryland"): states.Add ("Maine")
        states.Add ("Michigan"): states.Add ("Minnesota"): states.Add ("Missouri")
        states.Add ("Mississippi"): states.Add ("Montana")
        states.Add ("North Carolina"): states.Add ("North Dakota")
        states.Add ("Nebraska"): states.Add ("New Hampshire")
        states.Add ("New Jersey"): states.Add ("New Mexico"): states.Add ("Nevada")
        states.Add ("New York"): states.Add ("Ohio"): states.Add ("Oklahoma")
        states.Add ("Oregon"): states.Add ("Pennsylvania")
        states.Add ("Rhode Island"): states.Add ("South Carolina")
        states.Add ("South Dakota"): states.Add ("Tennessee"): states.Add ("Texas")
        states.Add ("Utah"): states.Add ("Virginia"): states.Add ("Vermont")
        states.Add ("Washington"): states.Add ("Wisconsin")
        states.Add ("West Virginia"): states.Add ("Wyoming")
        
        For counter = states.Count To 1 Step -8
            MsgBox states.item(counter)
        Next counter
      
        Set states = Nothing
    End Sub
  12. Return to Microsoft Access and click the button
  13. Click OK on each message box
  14. Return to Microsoft Visual Basic

Loop Counters and Procedures

Introduction

You can create a looping statement in a procedure or in an event of a control. When you do this, you may let the whole loop be processed.

Practical LearningPractical Learning: Stepping the Counting Loop

  1. To process a loop in a procedure, change the code as follows:
    Private Sub Show()
        Dim counter As Integer
        Dim states As New Collection
        
        states.Add ("Alaska"): states.Add ("Alabama"): states.Add ("Arkansas")
        states.Add ("Arizona"): states.Add ("California"): states.Add ("Colorado")
        states.Add ("Connecticut"): states.Add ("Delaware"): states.Add ("Florida")
        states.Add ("Georgia"): states.Add ("Hawaii"): states.Add ("Iowa")
        states.Add ("Idaho"): states.Add ("Illinois"): states.Add ("Indiana")
        states.Add ("Kansas"):  states.Add ("Kentucky"): states.Add ("Louisiana")
        states.Add ("Massachusetts"): states.Add ("Maryland"): states.Add ("Maine")
        states.Add ("Michigan"): states.Add ("Minnesota"): states.Add ("Missouri")
        states.Add ("Mississippi"): states.Add ("Montana")
        states.Add ("North Carolina"): states.Add ("North Dakota")
        states.Add ("Nebraska"): states.Add ("New Hampshire")
        states.Add ("New Jersey"): states.Add ("New Mexico"): states.Add ("Nevada")
        states.Add ("New York"): states.Add ("Ohio"): states.Add ("Oklahoma")
        states.Add ("Oregon"): states.Add ("Pennsylvania")
        states.Add ("Rhode Island"): states.Add ("South Carolina")
        states.Add ("South Dakota"): states.Add ("Tennessee"): states.Add ("Texas")
        states.Add ("Utah"): states.Add ("Virginia"): states.Add ("Vermont")
        states.Add ("Washington"): states.Add ("Wisconsin")
        states.Add ("West Virginia"): states.Add ("Wyoming")
        
        For counter = 1 To states.Count
            MsgBox states.item(counter)
        Next counter
      
        Set states = Nothing
    End Sub
    
    Private Sub Command0_Click()
        Show
    End Sub
  2. Return to Microsoft Access and click the button
  3. Click OK on each message box
  4. Return to Microsoft Visual Basic
  5. To create a function that returns a collection after processing a loop, change the code as follows:
    Private Function CreateStates() As Collection
        Dim states As New Collection
        
        states.Add ("Alaska"): states.Add ("Alabama"): states.Add ("Arkansas")
        states.Add ("Arizona"): states.Add ("California"): states.Add ("Colorado")
        states.Add ("Connecticut"): states.Add ("Delaware"): states.Add ("Florida")
        states.Add ("Georgia"): states.Add ("Hawaii"): states.Add ("Iowa")
        states.Add ("Idaho"): states.Add ("Illinois"): states.Add ("Indiana")
        states.Add ("Kansas"):  states.Add ("Kentucky"): states.Add ("Louisiana")
        states.Add ("Massachusetts"): states.Add ("Maryland"): states.Add ("Maine")
        states.Add ("Michigan"): states.Add ("Minnesota"): states.Add ("Missouri")
        states.Add ("Mississippi"): states.Add ("Montana")
        states.Add ("North Carolina"): states.Add ("North Dakota")
        states.Add ("Nebraska"): states.Add ("New Hampshire")
        states.Add ("New Jersey"): states.Add ("New Mexico"): states.Add ("Nevada")
        states.Add ("New York"): states.Add ("Ohio"): states.Add ("Oklahoma")
        states.Add ("Oregon"): states.Add ("Pennsylvania")
        states.Add ("Rhode Island"): states.Add ("South Carolina")
        states.Add ("South Dakota"): states.Add ("Tennessee"): states.Add ("Texas")
        states.Add ("Utah"): states.Add ("Virginia"): states.Add ("Vermont")
        states.Add ("Washington"): states.Add ("Wisconsin")
        states.Add ("West Virginia"): states.Add ("Wyoming")
    
        Set CreateStates = states
        Set states = Nothing
    End Function
    
    Private Sub Command0_Click()
        Dim counter As Integer
        Dim governments As Collection
        
        Set governments = CreateStates()
        
        For counter = 1 To governments.Count
            MsgBox governments.item(counter)
        Next counter
        
        Set governments = Nothing
    End Sub
  6. Return to Microsoft Access and click the button
  7. Click OK on each message box
  8. Return to Microsoft Visual Basic

Exiting a Procedure

In some cases, you may want to exit a conditional statement or a loop before its end. To assist with with this, the Visual Basic language provides the Exit keyword. It can be used in the body of a procedure or a loop.

Exiting a procedure consists of interrupting its flow and jumping to the End line of the procedure. To do this anywhere in the body of a procedure, type Exit Sub.

Practical LearningPractical Learning: Exiting a Procedure

  1. To create a procedure that exits before its end, change the code as follows:
    Private Sub ProcessTimeZones()
        Dim i As Integer
        Dim list As String
        Dim zones As Collection
    
        Set zones = New Collection
    
        list = "United States Times Zones" & vbCrLf & _
               "=-=-=-=-=-=-=-=-=-=-=-=-=-=-=" & vbCrLf
               
        zones.Add ("Pacific Standard Time (PST)")
        zones.Add ("Mountain Standard Time (MST)")
        zones.Add ("Central Standard Time (CST)")
        zones.Add ("Eastern Standard Time (EST)")
        zones.Add ("Atlantic Standard Time (AST)")
        zones.Add ("Alaskan Standard Time (AKST)")
        zones.Add ("Hawaii-Aleutian Standard Time (HST)")
        zones.Add ("Samoa standard time (UTC-11)")
        zones.Add ("Chamorro Standard Time (UTC+10)")
    
        MsgBox zones.item(1)
        MsgBox zones.item(2)
        MsgBox zones.item(3)
        MsgBox zones.item(4)
        MsgBox zones.item(5)
        MsgBox zones.item(6)
        MsgBox zones.item(7)
    
        Exit Sub
        
        MsgBox zones.item(8)
        MsgBox zones.item(9)
        
        Set zones = Nothing
    End Sub
    
    Private Sub Command0_Click()
        ProcessTimeZones
    End Sub
  2. Return to Microsoft Access and click the button
  3. Click OK on the message boxes.
    Notice that the collection contains 9 items but only 7 message boxes display
  4. Close the form
  5. When asked whether you want to save, click No
  6. On the Ribbon, click File and click Open
  7. In the list of files, click Business Starter from Lesson 13
  8. On the Ribbon, click Create and click Class Module
  9. Type:
    Public EmployeeNumber As Long
    Public FirstName As String
    Public LastName As String
    Public HourlySalary As Double
  10. In the Project window, click (Name) and type Employee
  11. In Project window, expand Microsoft Access Class Objects and double-click Form_Payroll Evaluation

Exiting a For Loop Counter

You can also exit a For loop that uses a counter. To do this, in the section where you want to stop, type Exit For. This would stop the flow of the loop and jump to the line immediately after the Next keyword. This feature of the Visual Basic language is very valuable when looking for a specific value or object in a collection.

Practical LearningPractical Learning: Exiting a Loop

  1. In the Object combo box, select cmdFind and implement the event as follows:
    Private Sub cmdFind_Click()
        Dim index As Integer
        Dim staff As Employee
        Dim worker As New Employee
        Dim employeeFound As Boolean
        Dim employees As New Collection
            
        employeeFound = False
          
        Set staff = New Employee
        staff.EmployeeNumber = "953-084"
        staff.FirstName = "Anthony"
        staff.LastName = "Walters"
        staff.HourlySalary = 18.52
        employees.Add staff
            
        Set staff = New Employee
        staff.EmployeeNumber = "204-815"
        staff.FirstName = "Jeannine"
        staff.LastName = "Rocks"
        staff.HourlySalary = 22.74
        employees.Add staff
            
        Set staff = New Employee
        staff.EmployeeNumber = "728-411"
        staff.FirstName = "Aaron"
        staff.LastName = "Gibson"
        staff.HourlySalary = 35.08
        employees.Add staff
    
        Set staff = New Employee
        staff.EmployeeNumber = "528-492"
        staff.FirstName = "Stephen"
        staff.LastName = "Brothers"
        staff.HourlySalary = 24.93
        employees.Add staff
    
        Set staff = New Employee
        staff.EmployeeNumber = "303-415"
        staff.FirstName = "Laura"
        staff.LastName = "Edom"
        staff.HourlySalary = 15.75
        employees.Add staff
    
        ' Check every record in the collection of employees
        For index = 1 To employees.Count
            ' Get a reference to the current record
            Set worker = employees(index)
                
            ' If the employee number of the current record is the same as the user typed, ...
            If worker.EmployeeNumber = txtEmployeeNumber Then
                ' ... display the first and last names
                txtEmployeeName = worker.LastName & ", " & worker.FirstName
                ' ... and display the hourly salary
                txtHourlySalary = worker.HourlySalary
    
                ' Since the employee has been found, make a note.
                employeeFound = True
    
                ' Now that the employee has been found, there is no reason to keep looking. Exit the loop
                Exit For
            End If
            
            ' As long as we haven't finished with the loop and we haven't
            ' found the employee, continue with the loop
        Next
    
        If employeeFound = False Then
            txtEmployeeName = ""
            txtEmployeeNumber = ""
        End If
    End Sub
  2. In the Object combo box, select cmdCalculate and implement the event as follows:
    Private Sub cmdCalculate_Click()
        Dim netPay As Double
        Dim timeWorked As Double
        Dim hourlySalary As Double
        
        hourlySalary = CDbl(Nz(txtHourlySalary))
        timeWorked = CDbl(Nz(txtTimeWorked))
        netPay = hourlySalary * timeWorked
        
        txtNetPay = FormatNumber(netPay)
    End Sub
  3. Return to Microsoft Access and switch the form to Form View
  4. Click Employee # and type 204-815
  5. Click Find

    Exiting a For Loop Counter

  6. Click Time Worked and type 42.50
  7. Click Calculate

    Exiting a For Loop Counter

  8. Save and close the form
 
 
 

Loops Repeaters

Introduction

The Visual Basic language presents many variations of loops. They combine the Do and the Loop keywords.

Doing Something While a Loop is Running 

One of the formulas to perform a loop uses the Do... Loop While approach. The formula to follow is:

Do
    statement(s)
Loop While condition

This loop can be illustrated as follows:

Doing Something While a Loop is Running

The statement(s) would execute first. Then the condition would be checked. If the condition is true, then the statement(s) would execute again. The check-execution routine would continue as long as the condition is true. If/Once the condition becomes false, the statement will not execute anymore and the code will move beyond the loop.

As you may guess already, the condition must provide a way for it to be true or to be false. Otherwise, the looping would execute continually.

Practical Learning: Doing a Loop While Something is Happening

  1. On the Ribbon, click File and click Open
  2. In the list of files, click Business Mathematics
  3. In the Navigation Pane, right-click Investment Evaluation and click Design View
  4. On the form, right-click the Calculate button and click Build Event...
  5. In the Choose Builder dialog box, click Code Builder and click OK
  6. Implement the event as follows:
    Private Sub cmdCalculate_Click()
        Dim index
        Dim periods
        Dim payment
        Dim interest
        Dim compounded
        Dim futureValue
        Dim interestRate
        Dim presentValue
        Dim strDepreciation
        Dim payments As New Collection
        Dim interests As New Collection
        Dim compoundFrequency As Integer
    
        futureValue = CDbl(txtFutureValue)
        interestRate = CDbl(txtInterestRate) / 100#
    
        compounded = CInt(InputBox("Enter a number for the desired compound frequency:" & vbCrLf & _
                                   "1 - Daily" & vbCrLf & _
                                   "2 - Weekly" & vbCrLf & _
                                   "3 - Monthly" & vbCrLf & _
                                   "4 - Quarterly" & vbCrLf & _
                                   "5 - Semiannually" & vbCrLf & _
                                   "6 - Anually", _
                                   "Compound interest", "1"))
        compoundFrequency = iif(compounded = 1, 365, iif(compounded = 2, 52, iif(compounded = 3, 12, iif(compounded = 4, 4, iif(compounded = 5, 2, 1)))))
       
        periods = CDbl(txtPeriods)
        presentValue = futureValue / ((1 + interestRate / compoundFrequency) ^ (periods * compoundFrequency))
    
        index = 1
    
        Do
            payment = futureValue / ((1 + interestRate / compoundFrequency) ^ ((periods * compoundFrequency) - index))
            payments.Add (payment)
            index = index + 1
        Loop While index <= periods * compoundFrequency
    
        interest = payments(1) - presentValue
        interests.Add interest
    
        index = 2
    
        Do
            interest = payments(index) - payments(index - 1)
            interests.Add interest
            index = index + 1
        Loop While index <= periods * compoundFrequency
    
    
        txtCompounded = iif(compounded = 1, "Compounded Daily", _
                        iif(compounded = 2, "Compounded Weekly", _
                        iif(compounded = 3, "Compounded Monthly", _
                        iif(compounded = 4, "Compounded Quarterly", _
                        iif(compounded = 6, "Compounded Semi-Annually", _
                                            "Compounded Anually")))))
        txtPresentValue = FormatNumber(presentValue)
    
        strDepreciation = "Depreciation Schedule" & vbCrLf & _
                          "===================" & vbCrLf & _
                          "Period" & vbTab & "Interest" & vbTab & "Amount" & vbCrLf & _
                          "===================" & vbCrLf
            
        index = 1
    
        Do
            strDepreciation = strDepreciation & CStr(index) & vbTab & FormatNumber(interests(index)) & vbTab & FormatNumber(payments(index)) & vbCrLf
            
            index = index + 1
        Loop While index <= periods * compoundFrequency
        
        MsgBox strDepreciation, vbOKOnly Or vbInformation, "Depreciation Schedule"
    End Sub
  7. Return to Microsoft Access and switch the to Form View

    Doing Something While a Loop is Running

  8. Click Future Value and type 5000
  9. Click Interest Rate and type 7.50
  10. Click Periods and type 5
  11. Click the Calculate button
  12. In the input box, type 3

    Doing Something While a Loop is Running

  13. Click OK

    Doing Something While a Loop is Running

  14. Save and close the form

Doing a Loop Until Something Happens

An alternative to the Do... Loop While uses the following formula:

Do
    statement(s)
Loop Until condition

Once again, the statement(s) section executes first. After executing the statement(s), the condition is checked. If the condition is true, the statement(s) section executes again. This will continue until the condition becomes false. Once the condition becomes false, the loop stops and the flow continues with the section under the Loop Until line.

Practical Learning: Doing a Loop Until Something Happens

  1. In the Navigation Pane, right-click Straight-Line Depreciation and click Design View
  2. On the form, right-click the Calculate button and click Build Event...
  3. In the Choose Builder dialog box, click Code Builder and click OK
  4. Implement the event as follows:
    Private Sub cmdCalculate_Click()
        Dim cost
        Dim period
        Dim bookValue
        Dim strSchedule
        Dim salvageValue
        Dim estimatedLife
        Dim depreciationRate
        Dim yearlyDepreciation
    
        period = 1
        cost = (txtCost)
        salvageValue = CDbl(txtSalvageValue)
        estimatedLife = CInt(txtEstimatedLife)
    
        depreciationRate = 100# / estimatedLife
        yearlyDepreciation = SLN(cost, salvageValue, estimatedLife)
    
        bookValue = cost
    
        strSchedule = "Depreciation Schedule" & vbCrLf & _
                      "======================================" & vbCrLf & _
                      "    Depreciation" & vbTab & "Yearly" & _
                      vbTab & vbTab & vbTab & "Accumulated" & vbCrLf & _
                      "Year" & vbTab & "Rate   Depreciation Book Value " & vbTab & "Depreciation" & vbCrLf & _
                      "======================================" & vbCrLf
                          
        Do
            strSchedule = strSchedule & period & vbTab & depreciationRate & "%" & vbTab & _
                          FormatNumber(yearlyDepreciation) & vbTab & _
                          FormatNumber(Format(bookValue - (yearlyDepreciation * period), "#")) & _
                          vbTab & vbTab & FormatNumber(yearlyDepreciation * period) & vbCrLf
    
            period = period + 1
        Loop Until period = estimatedLife + 1
    
        MsgBox strSchedule, vbOKOnly Or vbInformation, "Depreciation Schedule"
    End Sub
  5. Return to Microsoft Access and switch the to Form View
  6. Click Asset Original Value and type 5800
  7. Click Salvage Value and type 2000
  8. Click Periods and type 5

    Doing a Loop Until Something Happens

  9. Click the Calculate button
  10. In the input box, type 3

    Doing a Loop Until Something Happens

  11. Save and close the form

Doing a Loop While Something is Happening

The Visual Basic language provides another loop option that uses the Do While... Loop expression. Its formula is:

Do While condition
    statement(s)
Loop

This time, the condition is checked first. If the condition is true, then the statement(s) execute(s). Then the condition is checked again. If the Condition is false, or once/when the condition becomes false, the statement(s) section is(are) skipped and the flow continues with the code below the Loop keyword.

Practical Learning: Doing a Loop Until Something Happens

  1. On the Ribbon, click File and click Open
  2. In the list of files, click Chemistry1 from Lesson 13
  3. On the Ribbon, click Create and click Class Module
  4. Type:
    Public AtomicNumber As Integer
    Public ChemicalSymbol As String
    Public ElementName As String
    Public AtomicMass As String
  5. In the Project window, click (Name) and type Element
  6. Close Microsoft Visual Basic
  7. On the Ribbon, click Create and click Form Design
  8. On the Ribbon, click Button and click the form. If a wizard starts, click Cancel
  9. On the form, right-click the button and click Build Event...
  10. In the Choose Builder dialog box, click Code Builder and click OK
  11. Implement the event as follows:
    Private Sub Command0_Click()
        Dim strSummary
        Dim elm As Element
        Dim nbr As Integer
        Dim current As Element
        Dim periodicTable As New Collection
    
        Set elm = New Element
        elm.AtomicNumber = 1
        elm.ChemicalSymbol = "H"
        elm.ElementName = "Hydrogen"
        elm.AtomicMass = 1.0079
        periodicTable.Add elm
    
        Set elm = New Element
        elm.AtomicNumber = 2
        elm.ChemicalSymbol = "He"
        elm.ElementName = "Helium"
        elm.AtomicMass = 4.002682
        periodicTable.Add elm
    
        Set elm = New Element
        elm.AtomicNumber = 3
        elm.ChemicalSymbol = "Li"
        elm.ElementName = "Lithium"
        elm.AtomicMass = 6.941
        periodicTable.Add elm
    
        Set elm = New Element
        elm.AtomicNumber = 4
        elm.ChemicalSymbol = "Be"
        elm.ElementName = "Berylium"
        elm.AtomicMass = 9.0122
        periodicTable.Add elm
    
        Set elm = New Element
        elm.AtomicNumber = 5
        elm.ChemicalSymbol = "B"
        elm.ElementName = "Boron"
        elm.AtomicMass = 10.811
        periodicTable.Add elm
    
        Set elm = New Element
        elm.AtomicNumber = 6
        elm.ChemicalSymbol = "C"
        elm.ElementName = "Carbon"
        elm.AtomicMass = 12.011
        periodicTable.Add elm
    
        Set elm = New Element
        elm.AtomicNumber = 7
        elm.ChemicalSymbol = "N"
        elm.ElementName = "Nitrogen"
        elm.AtomicMass = 14.007
        periodicTable.Add elm
    
        Set elm = New Element
        elm.AtomicNumber = 8
        elm.ChemicalSymbol = "O"
        elm.ElementName = "Oxygen"
        elm.AtomicMass = 15.999
        periodicTable.Add elm
    
        strSummary = "Chemistry - Periodic Table" & vbCrLf & _
                     "=+=+=+=+=+=+=+=+=+=+=+=" & vbCrLf & _
                     "Atomic #" & vbTab & "Symbol" & vbTab & "Element" & _
                     vbTab & "Mass" & vbCrLf
    
        nbr = 1
        
        Do While nbr <= periodicTable.Count
            Set current = periodicTable(nbr)
            
            strSummary = strSummary & current.AtomicNumber & vbTab & _
                         current.ChemicalSymbol & vbTab & current.ElementName & _
                         vbTab & current.AtomicMass & vbCrLf
            
            nbr = nbr + 1
        Loop
        
        MsgBox strSummary, vbOKOnly Or vbInformation, _
               "Chemistry - Periodic Table"
    End Sub
  12. Return to Microsoft Access and switch the to Form View
  13. Click the button:

    Doing a Loop While Something is Happening

  14. Save and close the form

Do Something Until a Loop Ends

An alternative to the Do While... Loop loop uses the following formula:

Do Until condition
    statement(s)
Loop

This loop works like the Do While... Loop expression. The condition is examined first. If the condition is true, then the statement(s) section executes.

Practical Learning: Ending the Lesson

  • Close Microsoft Access
 
 
   
 

Previous Copyright © 2002-2016, FunctionX, Inc. Next