O'Reilly logo

Programming Excel with VBA and .NET by Steve Saunders, Jeff Webb

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Loops

The other fundamental part of programming logic is the ability to repeat a set of actions until some condition is met. This type of logic is called looping . I talked a little about loops in Chapter 1; here I’ll show you the different types of loops Visual Basic provides. Table 2-8 lists the looping statements in Visual Basic.

Table 2-8. Visual Basic statements for repeating actions

Statement

Use to

Do...Loop or While...Wend

Repeat a set of actions until a condition is met

For...Next

Repeat a set of actions a number of times using a counter

For Each

Repeat as set of actions for each item in a collection

Do...Loop and While...Wend are similar, but Do...Loop is more flexible so most programmers simply ignore While...Wend. I’m going to follow their lead—if you’re interested, you can read about While...Wend in Help.

The Do...Loop statement repeats a set of actions until a condition is met; you can include the condition at the beginning or the end of the loop. For example, the following code speaks any words you type in an input box and exits if you don’t type anything:

    Sub RepeatAfterMe( )
        Dim str As String
        Do
            str = InputBox("Enter something to say.", "Repeat after me...")
            Application.Speech.Speak str
        Loop While str <> ""
    End Sub

The preceding code executes the Speak method one time more than it really needs to —after the user cancels the input. This isn’t a big problem, but you can avoid it by using an Exit Do statement instead of testing ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required