Programming Excel with VBA and .NET

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