Wednesday, April 23, 2014

Getting rid of a GoTo

Issue

One of the things ingrained in me when I first studied programming was that GoTo should be avoided if at all possible

Original Code

The purpose of this Sub is  to walk through all of the files in a directory then pass the reference to the file to a Sub that tries to process it.  If the processing is successful then, if possible, move it to a different directory to prevent re-processing the file on a later run.
    
    filelongname = dir(Inputs_Dir & IIf(Right(Inputs_Dir, 1) = "\", "", "\"), vbReadOnly + vbHidden + vbSystem)
    Do While filelongname <> ""
        FCount = FCount + 1
        FileProcessed = False
        Call ProcessInputFile(filelongname, FileProcessed)
        '   Get next file
        If FileProcessed Then
            On Error GoTo CannotMove
            FileCopy Inputs_Dir & filelongname, Processed_Dir & filelongname
            Kill Inputs_Dir & filelongname
            FCount = FCount + 1
            On Error GoTo 0
CannotMove:
        Else
        End If
        filelongname = dir
    Loop

GoTo-less Code

By inspecting the Err object a decision is made whether to increase the number of files processed instead of skipping over it with a  GoTo which is a de facto-If.

Note that this procedure has two other differences
  • Use of the Name function instead of the FileCopy/Kill method.  See Moving Files: Two Methods.
  • The On Error GoTo 0 was moved outside the loop.  It was redundant inside because execution of the On Error Resume Next resets the Err object.  On the outside it resets the Err object and turns on error interruption mode .
    filelongname = dir(Inputs_Dir & IIf(Right(Inputs_Dir, 1) = "\", "", "\"), vbReadOnly + vbHidden + vbSystem)
    Do While filelongname <> ""
        FCount = FCount + 1
        FileProcessed = False
        Call ProcessInputFile(filelongname, FileProcessed)
        '   Get next file
        If FileProcessed Then
            On Error Resume Next
            Name Inputs_Dir & filelongname as Processed_Dir & filelongname
            If Err.Number = 0 Then FCount = FCount + 1  
        Else
        End If
        filelongname = dir
    Loop
    On Error GoTo 0