Microsoft Office Tutorials and References
In Depth Information
Do…Loop…Until
do…loop…until
Similar in approach to the Do…Loop…While construct, the Do…Loop…Until loop tests its condition
after executing the loop’s statements. The Until keyword tells VBA that the statements within the
loop will be executed again, for as long as the logical condition evaluates to False. Once VBA tests
the condition as True, the loop’s iterations will stop, and the macro will resume with the line of code
following the Loop keyword.
This macro shows an example of a Do…Loop…Until structure, which creates 365 new worksheets, all
named with dates starting from the day you run the macro:
Sub YearSheets()
Dim i As Integer
i = 0
Do
Sheets.Add(After:=Sheets(Sheets.Count)).Name = Format(VBA.Date + i, “MM-DD-YYYY”)
i = i + 1
Loop Until i = 365
End Sub
While…Wend
While…Wend loops have become obsolete and are rarely used because they are not as robust as Do
and For loops. VBA still supports While…Wend loops for backward compatibility with prior versions
of Excel, and I am not aware of any plans by Microsoft to stop supporting While…Wend .
So, though I recommend you not bother learning how to build a While…Wend loop, the fact is, they
are rather uncomplicated constructs and you should have some familiarity with how they look if
and when you see them in older code written by others. Here is an example of While…Wend that uses
an InputBox that asks for a password, and keeps asking until the correct password is entered, or the
message box is cancelled:
Sub InputPassword()
While InputBox(“Please enter password:”, “Password required”) <> “MyPassword”
If MsgBox( _
“Sorry, that is not correct.”, _
vbOKCancel, _
“Wrong password”) _
= vbCancel Then End
Wend
MsgBox “Yes!! You entered the correct password!”, vbOKOnly, “Thank you!”
End Sub
nEsTing loops
Your macros will eventually require that you enclose one loop structure inside another loop
structure, referred to as nesting loops. For example, you may need to loop through a set of rows in a data
table, and each completed set of looped-through rows will represent a single iteration for a larger
loop construct for the columns in the table.
Search JabSto ::




Custom Search