'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
Do
'your VB statements here
Loop
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.
Do
'your VB statements here, including one or more Exit Do statements
Loop While False
With Harfang's trick, you use the Exit Do statement to gracefully exit from this single-execution Do...Loop structure.
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
For Example Notes:
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
Do Example Notes:
'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 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
Notes:
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
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
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.
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
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.
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.
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.