<

Next Iteration functionality in VB classic and VBA

Published on
25,123 Points
11,923 Views
7 Endorsements
Last Modified:
Awarded
Community Pick

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
Comment
Author:aikimark
  • 2
3 Comments
 
LVL 38

Expert Comment

by:PaulHews
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

0
 
LVL 47

Author Comment

by:aikimark
@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.
0
 
LVL 38

Expert Comment

by:PaulHews
>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.


0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Join & Write a Comment

As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month