Community Pick: Many members of our community have endorsed this article.
Editor's Choice: This article has been selected by our editors as an exceptional contribution.

Next Iteration functionality in VB classic and VBA

aikimarkGet vaccinated; Social distance; Wear a mask
CERTIFIED EXPERT
Published:

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

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 

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.  
Since we want this Do Group to be executed at least once, we need to add the condition to the Loop statement.
Since we want this Do Group to be executed at most one time, we need to supply a terminating condition.
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

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

Open in new window

For Example Notes:
This example will execute the Debug.Print statement for all lngLoop values except 3.
Since the For...Next structure takes care of the initialization, iteration variable changes, and termination, you could use a code template without any consideration for placing statements between the For and the Do statements or between the Loop and Next statements.

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

Open in new window

Do Example Notes:
This example will execute the Debug.Print statement for all records except the quality control records (QC=True) in the recordset.
Since the Do...Loop structure only checks termination conditions, you would use a code template with some empty lines for placing statements between the Do and the Do (group) statements or between the Loop (group) and Loop statements.  In this example, we need to navigate to the next record in the recordset.

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

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

Open in new window

Notes:
DoNotProcessFile is some constant that will never equal an expected file data type value.
ValidFileType is a boolean function that will validate the file name as valid for processing.

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

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

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

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.
7
13,916 Views
aikimarkGet vaccinated; Social distance; Wear a mask
CERTIFIED EXPERT

Comments (3)

Commented:
I know that programming style is always a matter of taste, so take this for what it's worth.  I'm not a real purist when it comes to structured programming, and sometimes my subs and functions have more than one exit point.

I think if I was the person responsible for maintaining this code, I would probably see the loop, wonder what it's for, miss the "Exit Do" statement, comment out the loop, (because it looks useless, executing only once) try to compile and then swear like a trooper that I missed the "Exit Do" statement.  (Admittedly, I do a lot of swearing when maintaining other people's code.)

Commenting heavily could fix the maintenance problem, but I confess that I like code that does what it appears to do.  Loops are meant for looping.

If [condition] Then ... seems the most structured, natural way to conditionally execute a block of code.  

Since you are already setting the strDataType variable within the file type test block, it seems to me that you could use it to flag when the file type should not be processed.  If I didn't like the fact that there is so much code between the "If ... Then" and the "End If" then I would extract that code to its own subroutine.  If I couldn't co-opt the strDataType variable for whatever reason, I would create a flag variable specific to this use.
'open the csv files and glom the column headers and read the data
For intFileLoop = 0 To flstDAT_Files.ListCount - 1
    If UCase(flstDAT_Files.List(intFileLoop)) Like "*AMP.CSV" Then
        strDataType = GSeriesType_SZPP_Magnitude
    ElseIf UCase(flstDAT_Files.List(intFileLoop)) Like "*PHASE.CSV" Then
        strDataType = GSeriesType_SZPP_Phase
    Else
        strDataType = DoNotProcessFile
    End Select
    
    If strDataType <> DoNotProcessFile Then
      intFilenum = FreeFile
      intHeaderCount = 0
      Open MOF_Directories(PlotFileOpenCount).Dirname & "\" & flstDAT_Files.List(intFileLoop) For Input As #intFilenum
      Do
        Input #intFilenum, varData
        If Not IsNumeric(varData) Then
          intHeaderCount = intHeaderCount + 1
          strHeaders(intHeaderCount) = varData
        End If
      Loop Until IsNumeric(varData)
        '~~~~~~~~~
        'Other data processing takes place here
    End If
Next

Open in new window

aikimarkGet vaccinated; Social distance; Wear a mask
CERTIFIED EXPERT
Top Expert 2014

Author

Commented:
@Paul

Thank you for that example.  I'd tried to post several alternative implementations and forgotten to use this single-IF case.  I'm getting ready to tweak the two production code examples, making them a bit more generic.

For the simple case (one no-process condition), you are correct.  The simplest processing bypass would be implemented by your code.  With your permission, I'll include this in my tweak.

The nice thing about the Exit Do (group) solution is that it easily handles multiple ITERATE conditions.

The "<<Do Group" and ">>Do Group" comments exist to help the next progammer understand what I'm doing with this structure.

Of course, the Exit Do comment wouldn't include anything about Harfang's trick, but would include an "ITERATE", "CONTINUE", or "next intFileLoop" comment.

Commented:
>With your permission

Not an issue.  :)

>easily handles multiple ITERATE conditions.

I can see that necessity for some cases, and it's a shame that VB classic lacks that particular feature.


Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.