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 dataFor 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 typeNext intFileLoop
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.
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
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 RecordsetSet 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
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 dataFor 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 GroupNext intFileLoop
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 dataFor 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 IfNext intFileLoop
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 LongFor 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
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 ControlFor 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 IfnextControl:Next
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 VariantDim intFN As IntegerDim lngRow As LongintFN = FreeFileOpen "C:\Temp\2Bimported.csv" For Input As #intFNDo 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 IfLoopClose #intFN
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.
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 dataFor 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 IfNext
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.
Comments (3)
Commented:
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 in new window
Author
Commented: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:
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.