Next Iteration functionality in VB classic and VBA

Introduction

I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were causing me trouble.  In some languages, I would use a Continue or Iterate statement.  While I could accomplish this with my usual VB program structures and statements (some quite messy), I remembered a neat trick I'd seen in an EE article that might be an ideal solution and new addition to my programming structures.

If you are a beginner or interested in VB programming history, I've included examples of Next Iteration implementations in the Next Iteration in Classic VB section following the Conclusion.

Harfang's Smart Combobox article used a neat trick with the Do statement.  His trick is to create a single-execution Do...Loop (what I'm calling a Do Group) with multiple Exit Do statements.  I used his trick to create a well-structured 'next-iteration' functionality.

Note: The Continue Statement was a new feature introduced in VB.NET 2005

Background

One of my VB classic applications plots data that is read in from CSV files, created by a separate C++ application.  Recently, we added a new feature that rendered different (CSV) data as a 2D animation.  This is a different feature than graphing.  The testing of that new feature went well. Last weekend the tester reported a problem with the graphing feature.  It was failing and crashing the entire application.  Since I hadn't touched the graphing code in several releases, I was shocked that this code would suddenly start giving us problems.

Problem Determination

The tester sent me a directory of test files (output from the C++ application), so I started the graphing process on the development PC.  Sure enough, the application failed with a subscript out of range error in the drawing routine.  It took me a few runs before I traced the problem back to the data input routine.  When we added the new 2D animation feature, we also introduced CSV files into the directory that weren't there in the prior versions of the application.  The graphing code was reading the 'movie' files as if they were sets of graphing data!

In the following code snippet, a file list control's files are being opened and the data being read into the application's data structures.  Part of the processing is the classification of data, based on the file name pattern (*AMP* or *PHASE*).  

'open the csv files and glom the column headers and read the data
                      For intFileLoop = 0 To flstDAT_Files.ListCount - 1
                        intFilenum = FreeFile
                        Open strPath & "\" & flstDAT_Files.List(intFileLoop) For Input As #intFilenum
                      
                        'Process file header line
                      
                        'Assign processing type, based on file name  
                      
                        'Other file data processing takes place here, based on processing type
                      
                      Next intFileLoop
                      

Select all Open in new window

Problem Resolution

I needed to exclude the reading of these 'movie' files.  The Select Case statement seemed to contain the correct selection logic.  Now I had to restructure the code within my For...Next loop.  Here is where the Harfang trick comes into play.

Even though I do a lot of VB coding, my favorite language is REXX.  One of the nice programming structures in that language is an ITERATE statement.  When executed within one of the looping structures (Do...End), the execution immediately drops down to the End statement, bypassing all code following the ITERATE statement.

Creating the Do Group -- the TRICK

This 'trick' starts with one of the VB structures, a Do...Loop pair.

  Do 
                          'your VB statements here
                        Loop 
                      

Select all Open in new window

The 'trick' is that we are using a looping structure as a single-execution code 'container'.  The problem with this statement pair is the lack of a terminating condition.  While this will compile, it will loop endlessly.  We need to add either a While or Until clause to either the Do statement or the Loop statement.  
NOTE: I'm going to use a While clause, which means the terminating condition must be False.  If I were to use an Until clause, the terminating condition would be True.

  Do 
                          'your VB statements here, including one or more Exit Do statements
                        Loop While False
                      

Select all Open in new window

With Harfang's trick, you use the Exit Do statement to gracefully exit from this single-execution Do...Loop structure.

Since I'm creating an ITERATE functionality, I need to place my Do Group inside some other looping structure.

Example use with For...Next:

Dim lngLoop As Long
                      
                      For lngLoop = 1 To 10
                      
                        Do '<<Do Group
                          If lngLoop = 3 Then Exit Do 'Group
                          Debug.Print lngLoop
                        Loop While False '>>Do Group
                      
                      Next
                      

Select all Open in new window

For Example Notes:

Example use with Do...Loop:

Dim rs As Recordset
                      Set rs = CurrentDb.OpenRecordset("Select * From Patients Where Active = True")
                      
                      Do Until rs.EOF
                        
                        Do '<<Do Group
                          If rs.Fields("QC") = True Then Exit Do 'Group
                          Debug.Print rs.Fields("PatientName")
                        Loop While False '>>Do Group
                        
                        rs.MoveNext 
                      
                      Loop
                      

Select all Open in new window

Do Example Notes:

The ITERATE Feature Now in Production

Here is the production version of the code, with the relocated and modified Select Case structure -- it now has a new Case Else clause.  I've also placed the code in the For...Next loop inside a Do Group structure.

'open the csv files and glom the column headers and read the data
                      For intFileLoop = 0 To flstDAT_Files.ListCount - 1
                        Do '<< Do Group
                          'Assign processing type, based on file name  
                      
                          If Not ValidFileType Then Exit Do 'ITERATE => Next intFileLoop
                          
                          intFilenum = FreeFile
                          Open strPath & "\" & flstDAT_Files.List(intFileLoop) For Input As #intFilenum
                      
                          'Process file header line
                      
                          'Other file data processing takes place here, based on processing type
                      
                        Loop While False '>>Do Group
                      
                      Next intFileLoop
                      

Select all Open in new window

Alternative Work-around

PaulHews suggested I post an example of restructured production code.  In this example, there is a single If...EndIf protecting the file processing code.  Any number of prior conditions might be checked, in addition to ValidFileType, that could set the strDataType variable.  If your condition-checking is not dependent on prior validation, what I'll call 'flat', then this is actually a preferred work-around to the problem.  It is simpler to understand and maintain.

'open the csv files and glom the column headers and read the data
                      For intFileLoop = 0 To flstDAT_Files.ListCount - 1
                        'Assign data type, based on file name  
                        
                        'Set a flag variable to prevent later processing
                        If Not ValidFileType(flstDAT_Files.List(intFileLoop)) Then 
                          strDataType = DoNotProcessFile
                        End If
                      
                        If strDataType <> DoNotProcessFile Then
                          intFilenum = FreeFile
                          Open strPath & "\" & flstDAT_Files.List(intFileLoop) For Input As #intFilenum
                      
                          'Process file header line
                      
                          'Other file data processing takes place here
                      
                        End If
                          
                      Next intFileLoop
                      

Select all Open in new window

Notes:

Conclusion

Here is a new functionality you can incorporate into your VBA routines.  Using a Do group within your VB iteration structures (For...Next, For Each...Next, Do...Loop) will keep your code clean, not overly indented, well structured, and free of Go To statements.

If you found this article helpful, please click the Yes link below.
______________________________________________________________________________

Next Iteration in Classic VB -- techniques to be avoided

The three VB looping structures (For...Next, For Each...Next, Do...Loop) perform their function quite efficiently and reliably.  However, there are times when you don't want to process one or more items in the set (collection) being iterated.  Beginners and self-taught programmers have used the following techniques.

Increment the Iteration Variable
NOTE: Changing the iteration variable value inside a For...Next loop is a bad programming practice and should be avoided at all costs.

In this example, a row is 'skipped' by incrementing the interation variable, lngLoop.

Dim lngLoop As Long
                      For lngLoop = 1 To ActiveSheet.Rows.Count
                        If IsNumeric(ActiveSheet.Cells(lngLoop, 1).Value) Then
                          lngLoop = lngLoop + 1 'skip the separator row
                        End If
                      
                        'Process the cells on this row
                        ActiveSheet.Cells(lngLoop, 3).Value = Sqr((ActiveSheet.Cells(lngLoop, 1).Value ^ 2) + (ActiveSheet.Cells(lngLoop, 2).Value ^ 2))
                      
                      Next
                      

Select all Open in new window

Use a Go To
One of the first lessons taught in structured programming classes is "never use a Go To."  While this is an excellent rule-of-thumb, we frequently encounter the Go To statements of other programmers, posted online or inherited at our workplace.  Many VB Programmers have found the GoTo statement quite convenient for skipping over code they don't want to execute.

In this example, we see a programmer is clearing/resetting controls on a form, looping through the Controls collection.  I imagine that this code started out without the GoTo statements.  Then the programmer realized (or was told) that once a control is processed, there is no need to compare its type in the subsequent IF statements.

Dim ctlThing As Control
                      
                      For Each ctlThing In Me.Controls
                        
                        If TypeOf ctlThing Is TextBox Then
                          ctlThing.Text = vbNullString
                          ctlThing.BackColor = vbWhite
                          GoTo nextControl
                        End If
                        If TypeOf ctlThing Is ComboBox Then
                          ctlThing.Clear
                          ctlThing.Text = vbNullString
                          ctlThing.BackColor = vbWhite
                          GoTo nextControl
                        End If
                        If TypeOf ctlThing Is Label Then
                          If ctlThing.Tag Like "*reset;*" Then
                            ctlThing.Caption = vbNullString
                          End If
                          GoTo nextControl
                        End If
                        If TypeOf ctlThing Is Menu Then
                          If ctlThing.Checked Then
                            ctlThing.Checked = False
                          End If
                          GoTo nextControl
                        End If
                        If TypeOf ctlThing Is CheckBox Then
                          ctlThing.Value = vbUnchecked
                          GoTo nextControl
                        End If
                        If TypeOf ctlThing Is OptionButton Then
                          ctlThing.Value = False
                          GoTo nextControl
                        End If
                        If TypeOf ctlThing Is ListBox Then
                          ctlThing.Clear
                          GoTo nextControl
                        End If
                        If TypeOf ctlThing Is CommandButton Then
                          If ctlThing.Style = vbButtonGraphical Then
                            ctlThing.Picture = LoadPicture()
                          End If
                          GoTo nextControl
                        End If
                        
                      nextControl:
                      Next
                      

Select all Open in new window

Note: I have even encountered a form of this GoTo method using error-handling routines, using the Resume statement like a GoTo.  It is painful enough to remember.  I couldn't bring myself to replicate it in this article.  It was a nightmare to behold.

Use If...Else
In this example, the items on the CSV file's (current) row are validated prior to being inserted into the active worksheet.  

Notice that each IF statement's validation increases the indentation level.  It wouldn't take too many more input columns before the IF statements aren't visible without scrolling!

Dim vInputs(1 To 4) As Variant
                      Dim intFN As Integer
                      Dim lngRow As Long
                      intFN = FreeFile
                      Open "C:\Temp\2Bimported.csv" For Input As #intFN
                      Do Until EOF(intFN)
                          
                          Input #intFN, vInputs(1), vInputs(2), vInputs(3), vInputs(4)
                          If IsDate(vInputs(1)) Then
                          Else
                              If (vInputs(2) = "NEW" Or vInputs(2) = "UPD") Then
                                  If IsNumeric(vInputs(2)) Then
                                      If Len(Trim(vInputs(4))) <> 0 Then
                                          lngRow = lngRow + 1
                                          ActiveSheet.Range(Cells(lngRow, 1), Cells(lngRow, 4)) = vInputs
                                      End If
                                  End If
                              End If
                          End If
                      
                      Loop
                      Close #intFN
                      

Select all Open in new window

Note: When I see code like this, I usually try to refactor it, moving the validation into a separate function that receives the vInputs array as a parameter and returns a string/collection of validation error messages.  If there were no errors returned, then the update takes place.