Visual Basic for Applications/Loops

This lesson introduces loops.

Objectives and Skills

Objectives and skills for loops include:

Performing operations repetitively by using a Visual Basic Do...Loop structure
Performing operations repetitively by using Visual Basic For...Next and For Each...Next structures

Readings

Multimedia

Examples

For

'This macro uses a dynamic array to accept user input and display a total.

Option Explicit

Sub ForLoop()
    Const Title = "For Loop"
    Dim Value() As Variant
    Dim Total As Single
    Dim I As Integer
   
    ReDim Value(3)
    Total = 0
    For I = 1 To 3
        Value(I) = InputBox("Enter a value:", Title)
        Total = Total + Value(I)
    Next
    MsgBox "The total is: " & Total, vbOKOnly + vbInformation, Title
End Sub

Do

'This macro uses a Do...Loop to display the current time.

Option Explicit

Sub DoLoop()
    Const Title = "Do Loop"
   
    Do
        MsgBox "The time is: " & Time(), vbOKOnly + vbInformation, Title
    Loop While MsgBox("Do you want to see the current time?", vbYesNo + vbQuestion, Title) = vbYes
End Sub

Activities

In these activities you will create macros that use input boxes to obtain user input, variables, conditions, and loops to perform calculations, and message boxes to display results. Your macros should include Option Explicit, Dim, InputBox, MsgBox, titles on the dialog boxes, an icon on the message box, and use appropriate data types for your variables.

Average Calculation
1. Create a macro that asks the user to enter grade scores. Start by asking the user how many scores they would like to enter. Then use a loop to request each score and add it to a total. Finally, calculate and display the average for the entered scores.
2. Revise the script several times to compare the code required when the loop control structure is based on While, Do While, Do Until, and For Next statements, respectively.
Multiplication Table
1. Review MathsIsFun: 10x Printable Multiplication Table.
2. Create a macro that uses nested for loops to generate a multiplication table. Rather than simply creating a 10 by 10 table, ask the user to enter the starting and ending values.
Multiplication Table with Conditions
1. Review MathsIsFun: 10x Printable Multiplication Table.
2. Create a script that uses nested for loops to generate a multiplication table. Use a condition with (<variable> Mod 2) to test for odd or even values. Generate the multiplication table only for even values. For odd values, skip display and continue to the next iteration of the loop.
Multiplication Table with User Input
1. Review MathsIsFun: 10x Printable Multiplication Table.
2. Create a script that uses nested for loops to generate a multiplication table. Rather than simply creating a 10 by 10 table, ask the user to enter the starting and ending values. Use an Exit Do / Exit For statement to terminate the loop if the count exceeds 10.

References

← Conditions

Visual Basic for Applications

Arrays →