Thursday, January 15, 2015

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