Microsoft Office Tutorials and References
In Depth Information
The Do statement is an extremely powerful tool with which to gain more flexibility in your
looping structures. In a Do…While loop, you test for a condition that must be True before the loop will
execute. When the condition is True, the command(s) within the loop are executed.
As a simple example, the DoWhileExample macro will produce five message boxes because the Do…
While loop tests for the condition that an Integer variable (named iCounter ) has not exceeded the
number 5. Notice that the iCounter variable starts at 1 outside the loop and is increased by 1 inside
Dim iCounter As Integer
iCounter = 1
Do While iCounter <= 5
MsgBox “Hello world!”, , iCounter
iCounter = iCounter + 1
Applying this concept to a more practical activity, suppose you want to open all Excel workbooks
that are in a particular file path. The macro named OpenAllFiles will do that using a Do…Loop
structure. The Dir function returns the first filename that matches the combination of the specified
pathname and an Excel workbook extension containing .xls . Calling the Dir function again would
open additional filenames until a filename is encountered that does not match the combination.
Dim myFile As String, myPath As String
myPath = “C:\Your File Path\”
myFile = Dir(myPath & “*.xls*”)
Do While myFile <> “”
Workbooks.Open myPath & myFile
myFile = Dir()
When VBA runs a Do…Until loop, it tests the logical condition you supply and executes the commands
within the loop as long as the condition evaluates to False. When VBA reaches the Loop statement, it
re-evaluates the condition and executes the looping commands only if the condition is still False.
This example demonstrates Do…Until by selecting the next worksheet based on the index number
from whatever current worksheet you are on. The wrinkle that is taken into consideration by the loop
is that the next highest index number worksheet might be hidden, and because you cannot select a
hidden worksheet, the loop selects the next highest index number of a worksheet that is also visible.
‘Declare an Integer type variable to handle the Index number property
‘of whichever worksheet(s) are being evaluated in the current iteration.
Dim intWS As Integer
‘Because you want to activate the next visible worksheet,