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 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

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

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 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)
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:

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

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

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
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

Set house = New Property
house.PropertyNumber = 502840
house.PropertyType = HouseTypes.Condominium
house.City = "Washington"
house.Bedrooms = 2
house.Bathrooms = 2#
house.MarketValue = 318950

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

Set house = New Property
house.PropertyNumber = 240875
house.PropertyType = HouseTypes.Townhouse
house.City = "Charleston"
house.Bedrooms = 3
house.Bathrooms = 2.5
house.MarketValue = 348500

Set house = New Property
house.PropertyNumber = 475974
house.PropertyType = HouseTypes.SingleFamily
house.City = "Gaithersburg"
house.Bedrooms = 4
house.Bathrooms = 2.5
house.MarketValue = 635775

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:

39. Click OK on the message box
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

Set house = New Property
house.PropertyNumber = 502840
house.PropertyType = HouseTypes.Condominium
house.City = "Washington"
house.Bedrooms = 2
house.Bathrooms = 2#
house.MarketValue = 318950

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

Set house = New Property
house.PropertyNumber = 240875
house.PropertyType = HouseTypes.Townhouse
house.City = "Charleston"
house.Bedrooms = 3
house.Bathrooms = 2.5
house.MarketValue = 348500

Set house = New Property
house.PropertyNumber = 475974
house.PropertyType = HouseTypes.SingleFamily
house.City = "Gaithersburg"
house.Bedrooms = 4
house.Bathrooms = 2.5
house.MarketValue = 635775

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)
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 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

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
11. To loop backward, change the code as follows:
```Private Sub Command0_Click()
Dim counter As Integer
Dim states As New Collection

For counter = states.Count To 1 Step -8
MsgBox states.item(counter)
Next counter

Set states = Nothing
End Sub```
13. Click OK on each message box

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 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

For counter = 1 To states.Count
MsgBox states.item(counter)
Next counter

Set states = Nothing
End Sub

Private Sub Command0_Click()
Show
End Sub```
3. Click OK on each message box
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

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```
7. Click OK on each message box

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 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

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```
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 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

Set staff = New Employee
staff.EmployeeNumber = "204-815"
staff.FirstName = "Jeannine"
staff.LastName = "Rocks"
staff.HourlySalary = 22.74

Set staff = New Employee
staff.EmployeeNumber = "728-411"
staff.FirstName = "Aaron"
staff.LastName = "Gibson"
staff.HourlySalary = 35.08

Set staff = New Employee
staff.EmployeeNumber = "528-492"
staff.FirstName = "Stephen"
staff.LastName = "Brothers"
staff.HourlySalary = 24.93

Set staff = New Employee
staff.EmployeeNumber = "303-415"
staff.FirstName = "Laura"
staff.LastName = "Edom"
staff.HourlySalary = 15.75

' 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

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

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:

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))
index = index + 1
Loop While index <= periods * compoundFrequency

interest = payments(1) - presentValue

index = 2

Do
interest = payments(index) - payments(index - 1)
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```

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

13. Click OK

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```
6. Click Asset Original Value and type 5800
7. Click Salvage Value and type 2000
8. Click Periods and type 5

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

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

Set elm = New Element
elm.AtomicNumber = 2
elm.ChemicalSymbol = "He"
elm.ElementName = "Helium"
elm.AtomicMass = 4.002682

Set elm = New Element
elm.AtomicNumber = 3
elm.ChemicalSymbol = "Li"
elm.ElementName = "Lithium"
elm.AtomicMass = 6.941

Set elm = New Element
elm.AtomicNumber = 4
elm.ChemicalSymbol = "Be"
elm.ElementName = "Berylium"
elm.AtomicMass = 9.0122

Set elm = New Element
elm.AtomicNumber = 5
elm.ChemicalSymbol = "B"
elm.ElementName = "Boron"
elm.AtomicMass = 10.811

Set elm = New Element
elm.AtomicNumber = 6
elm.ChemicalSymbol = "C"
elm.ElementName = "Carbon"
elm.AtomicMass = 12.011

Set elm = New Element
elm.AtomicNumber = 7
elm.ChemicalSymbol = "N"
elm.ElementName = "Nitrogen"
elm.AtomicMass = 14.007

Set elm = New Element
elm.AtomicNumber = 8
elm.ChemicalSymbol = "O"
elm.ElementName = "Oxygen"
elm.AtomicMass = 15.999

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```
13. Click the button:

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