The Loops...Whys and Wherefores

Introduction

Yesterday I ran into a web article that showed the Do Until...Loop construct.  It gave a perfunctory example that went something like this.

Sub UseDoUntil()

    Dim i as Integer

    Do Until i > 6

        i =  i + 1

        Range("A" & i).Value = 2015

    Loop

End Sub

My thought was "So what?  What difference does it make?  Why is the Do Until used here instead of Do While i <= 6 or For i = 1 to 6 or even Do ... Loop Until i > 5?"

With that in mind, I went back to my reference books.  They did not do a good job of explaining when each should be used, either, so I decided to dig out from 3 decades of memory cells why each different type of loop should be used in different circumstances.

For...Next

The form for this loop is 

For <counter> = <start> To <end> [Step <step>]

   ...

Next <counter>

As a reminder 

• The <counter>, aka the control variable, is equal to <start> on  the first iteration. With each subsequent iteration the value of the control variable is changed by <step>.
• As indicated by the square brackets ("[]"), "Step <step>" portion of this form is optional.  It defaults to "Step 1" if not specified.
• Execution ends when <counter> meets or exceeds <end>.
• The Next statement may be made without <counter>.  However, this is not recommended because of problems with unexpected results and difficulty in troubleshooting nested For...Next constructs.
  
  For i = 1 To 6
      For j = 1 to 3         Cells(j,i).Value = 2015
      Next j
  Next i

For the case presented, putting values into a series of cells, using For...Next is an improvement because it is simpler, i.e. the test for whether the loop has completed is integrated with incrementing the control variable.

For i = 1 To 6

   Range("A" & 1).Value = 2015

Next i

For...Next with Arrays

Another use for the For ...Next form is when dealing with arrays.  In this case the Lbound() and Ubound() functions are used for <start> and <end>.

For i = LBound(Stores) To UBound(Stores)

   Range("A" & i).Value = Stores(i).Name

Next i

It would be possible to use Do Until i > UBound(Stores) but, as before, this is cleaner.  furthermore, because the lower bound of the Stores array may be 0, or 1, or any other integer, prior to the Do Until statement the code would need to set i to the lower bound of the array, e.g.      i = LBound(Stores) 

    Do Until i > UBound(Stores)

        ....

    Loop

For...Next with Negative <step>

My guess is that well more than 90% of the For...Next constructs have the implied "Step 1", and 90% of the remainder have a positive number for <step>.  However, it is possible to have a negative <step>.

When using a negative step, e.g. For i = <start> To <end> Step -2, 

• The value of <start> must be less than <end>.
• Execution ends when i less than or equal to <end>

Re-using the Stores array example from above

For i = UBound(Stores) To LBound(Stores) Step -1

   Range("A" & i).Value = Stores(i).Name

Next i

Another use would be to clear out every other row at the bottom of the worksheet

Dim LastCurrentRow

LastCurrentRow = UsedRange.Row + UsedRange.Rows.Count -1

For i = LastCurrentRow  To LastCurrentRow  - 10 Step -2

   Range.Rows(i:i).ClearAll

Next i

For Each...Next for Collections

The MS-Office model includes a number of Collections of objects, e.g. the Workbooks and Worksheets collections.  For these these is a special form of the For...Next, For Each <object> of <collection>.  For example, the following function 

• Cycles through the Workbooks collection, then for each workbook, cycles through its Worksheets collection
• Counts the number of worksheet names that include a given string, for example, any worksheet with the word "Budget" in its name.  

Function CountWorksheetsByGroupName(wsGroupName as String) as Integer

    Dim Wkbk as Workbook

    Dim Ws as Worksheet

    Dim CountedWS as Integer

    For Each Wkbk in Workbooks

        For Each Ws in Worksheets

            If Instr(1,ws.Name, wsName) > 0 then

                CountedWS  = CountedWS + 1

            End If

        Next Ws 

    Next Wkbk 

    CountWorksheetsByGroupName= CountedWS

End Function

For Each...Next for a Collection of a Range object

Within a Range object there are three collections useful in cycling through for repetitive action. (Note:  This discussion assumes a contiguous, rectangular Range.)

• Cells. 
• Rows.
• Columns.

The code in the original article could be rewritten by using a Range object's collection of Cells

Sub UseForEachCellsOfRange()

    Dim rng as Range 
    Dim cel as Range
    Set rng = Range("A1:A6")

    For each cel of rng.cells

        cel.Value = 2015

    Next cel

End Sub

"Do...Loop(s)"

There is a purpose and performance difference between For...Next and Do...Loop. 

• The For...Next structures are designed to execute for each valid value (member), no matter the conditions encountered.  
• Do Loop(s) are designed to exit once a condition is met

Do Until...Loop and Do While...Loop are interchangeable, for the most part, as are the Do ...Loop Until and  Do ...Loop While structures. As mentioned above, they should be used to check if a conditioned changed during the most recent iteration and then exit.   For examples,

Function StoreSalesMeetsMin(StoreName as String, MinSales as Double) as Boolean

    Dim i as Double

    Dim TempStoreSales as Double

    Dim MetMin as Boolean ' Defaults to not True

    Do Until MetMin

        ' Step through worksheet with daily sales to determine week-to-date sales

        i = i + 1 

        MetMin = StoreMetMin(TempStoreSales , Rows(i), MinSales)

    Loop

    StoreSalesMeetsMin = MetMin

End Function

Function StoreMetMin(ByRef StoreSales as Double, _

                              SalesRow as Range, _

                              ByVal MinSales as Double) as Boolean

    Dim i as Integer

    i = 2

    Do While StoreSales <=  MinSales 

        StoreSales = StoreSales + SalesRow.Cells(1,i).Value

    Loop

    If StoreSales > MinSales Then

        StoreMetMin = True

    End If

End Function

Do While v Do Until

Code is easier to understand, and a bit more efficient, if the condition with the Do is positive.  

• The Do Until MetMin in StoreSalesMeetsMin() could be re-written as Do While Not MetMin, but it is a little harder to read.
• The Do While StoreSales <=  MinSales in StoreMetMin could be re-written as Do Until Not StoreSales >=  MinSales, without affecting the readability.

Loop Until and Loop While

It may seem, at first glance, that putting the condition at the end of the loop instead of the beginning would simply make things harder to read.  It does.  However, there is a purpose.

When the condition is put at the end of the loop, the first test is not until the first iteration has executed.  This allows for a variable that is tested to be given a value prior to the test and not force bad programming like putting in giving starting values to variables then backing them out on first iteration.

Function InventoryAlert(PlannedChanges as Range, RowNumber as Double, _

                                Optional OnHandInventory as Variant) as Boolean

    ' OnHandInventory is optional for cases when new inventory locations are created

    Const SeedNumber as Integer =1

    Dim i as Integer

    If IsMissing(OnHandInventory) Then = OnHandInventory + SeedNumber 

    ' Check the planned transactions for any point when inventory will be fully depleted

    Do While OnHandInventory >= 0

       i = i + 1

       If i = 1 Then 

          OnHandInventory = OnHandInventory - SeedNumber 

       End If

       OnHandInventory = OnHandInventory  + Cells(RowNumber, i).Value

    Loop

    If OnHandInventory < 0 Then 

        InventoryAlert = True

    End If

End Function

Now, here is the right way!  Not only is there no need to seed the variable, it is now passed as an integer.

Function InventoryAlert(PlannedChanges as Range, RowNumber as Double, _                                Optional OnHandInventory as Integer) as Boolean

 ' OnHandInventory is optional for cases when new inventory locations are created

    Dim i as Integer

' Check the planned transactions for any point when inventory will be fully depleted

    Do

        i = i + 1

        OnHandInventory = OnHandInventory  + Cells(RowNumber, i).Value

    Loop Until OnHandInventory <= 0

    If OnHandInventory < 0 Then 

        InventoryAlert = True

    End If

End Function

When a For Each or Do...Loop(s) would do

One of the shortcomings of the For Each structure, when dealing with a collection, is it is often used to select an object based on a criteria, then it's work is done.  As mentioned above, the Do Loops are designed to exit when a condition is met.  On the other had, the For Each is especially suited for dealing with Collections.  Following are options for resolving this.

Option 1: Fixing the For Each

Below is function that returns a workbook that has a worksheet of a particular name.  Upon find the worksheet, the parent workbook is assigned ("Set") to the be returned, but continues to cycle through the remaining worksheets and workbooks.  (NOTE: This means the function will return the last encountered workbook that has a worksheet of the given name.  This is a flaw to be handled by means outside of the scope of this example.)

Function WorkbookByWorksheetName(wsName as String) as Workbook

    Dim Wkbk as Workbook

    Dim Ws as Worksheet

    For Each Wkbk in Workbooks

        For Each Ws in Worksheets

            If ws.Name = wsName Then

                Set WorkbookByWorksheetName= Wkbk 

            End If

        Next Ws 

    Next Wkbk 

End Function

Following are some methods to stop execution of For loops when all exit criteria have been met (NOTE: implementing any of the following fixes will result in the function returning the first encountered workbook that has a worksheet of the given name.  This is a flaw to be handled by means outside of the scope of this example.)

• After the criteria are met, e.g. the worksheet is found and set to the appropriate variable, insert the line Exit Function in the next line.  In this case it would go between Set WorkbookByWorksheetName= Wkbk and the End If. (See Below)
• Use an exit tag to send move control to the end of the loop(s).  In this case (See Below)
• Add a tag such as WorkbookByWorksheetName_Exit: such between the Next Wkbk line and the End Function line 
• After Set WorkbookByWorksheetName = Wkbk insert a line with a go to the exit tag, e.g. WorkbookByWorksheetName_Exit
• Use Exit For statements (See Below)
• If there is only one level of For Each statements, then a simple after making the exit criteria true, e.g. Set WorkbookByWorksheetName = Wkbk, insert the line Exit For
• If there are multiple levels of For Each structure, as in the example above, insert the Exit For as described above then immediately after the inner Next statement insert a line testing the condition and exiting as appropriate, e.g. If Not WorkbookByWorksheetName Is Nothing Then Exit For, then repeating at the exit points for any other overarching For Each structures.

Function WorkbookByWorksheetName(wsName as String) as Workbook

    Dim Wkbk as Workbook

    Dim Ws as Worksheet

    For Each Wkbk in Workbooks

        For Each Ws in Worksheets

            If ws.Name = wsName Then

                Set WorkbookByWorksheetName= Wkbk 
                Exit Function
                GoTo WorkbookByWorksheetName_Exit
                Exit For

            End If

        Next Ws 
        If Not WorkbookByWorksheetName Is Nothing Then Exit For

    Next Wkbk 

WorkbookByWorksheetName_Exit:

End Function

• Instead of using the For Each Ws in Worksheets loop (See Below: New code, Removed Code, methods of exiting at success-point highlighted as above), use 
• An On Error Resume Next just before Set WorkbookByWorksheetName = Wkbk
• Modify the Set command to Set WorkbookByWorksheetName = Wkbk.Worksheets(wsName).Parent
• After the Set command, reset the Error Code to 0.
• Finally, if the Set was successful then Not WorkbookByWorksheetName Is Nothing  will be true and any of the transfer methods above can be used.

Function WorkbookByWorksheetName(wsName as String) as Workbook

    Dim Wkbk as Workbook

    Dim Ws as Worksheet

    For Each Wkbk in Workbooks
        On Error Resume Next

        For Each Ws in Worksheets

            If ws.Name = wsName Then

       Set WorkbookByWorksheetName= Wkbk.Worksheets(wsName).Parent
         On Error GoTo 0
       If Not WorkbookByWorksheetName Is Nothing Then 
                ' Use one of the constructs from above
                          Exit Function
                     Exit For
                     WorkbookByWorksheetName_Exit
         End If

            End If

        Next Ws 
        If Not WorkbookByWorksheetName Is Nothing Then Exit For

    Next Wkbk 

WorkbookByWorksheetName_Exit:

End Function

These means may not fit your style since they use Exit Function, Exit For, On Error, and GoTo statements, violating the principles of structured programming.  However, I can sign-off on this choice, assuming 

1. These are to be used only in very small functions/subs, meaning simpler troubleshooting/maintenance and 
2. This is consistent throughout the project, also a troubleshooting and maintenance help.

Option 2: Using a Do...Loop

The alternative to violating structured programming principles is to use a structure not as directly intended for dealing with collections, a Do...Loop.  For this case, I am using only the Do...Loop Until form but the same can be done with Do...Loop While, Do While...Loop, and Do Until...Loop forms.

One of the attributes of the Workbooks and Worksheets collections is "Count", providing the number of Workbooks open and available for editing and the number of worksheets in each workbook.  Therefore, the Count can be used to exit the loop after all members of the collection have been considered.

    Dim Wkbk As Workbook

    Dim i_wkbk As Integer

    Dim i_Ws As Integer

    Do

        i_wkbk = i_wkbk + 1

        Set Wkbk = Workbooks(i_wkbk)

        i_Ws = 0

        Do

            i_Ws = i_Ws + 1

            If Wkbk.Worksheets(i_Ws).Name = wsName Then

                Set WorkbookByNameDoLoop = Wkbk

            End If

        Loop Until Not WorkbookByNameDoLoop Is Nothing Or i_Ws = Wkbk.Worksheets.Count

    Loop Until Not WorkbookByNameDoLoop Is Nothing Or i_wkbk = Workbooks.Count