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.