Search code examples
excelvba

End processing of a procedure when an error code is generated


I want to end the processing of a procedure if an initial test fails and an error code is generated.

In the example below I would need to indent the bulk of the procedure code in an ELSE clause inside an IF ENDIF.

Is there another way?

In some languages it is possible to issue a RETURN instruction to abort a procedure.

Excel VBA Example

Dim pcActiveSheet as String, plSheetError as Boolean

-------------------------------------------------------------
Sub NewCode
  
  pcActiveSheet = ActiveSheet

  SheetTest (pcActiveSheet, plSheetError)

  if plSheetError = TRUE
    
    NOW I JUST WANT END NEWCODE PROGRAM QUICKLY
    Instead of indenting all the rest of the code in an indented ELSE condition, as shown below,
    isn't there another way to end the processing of Sub Newcode right here 
    and return the user to the Worksheet?
   
  
  ELSE  
    ' Lots of code would go here..........
    ' If SheetError returns FALSE I know I can put the rest of code here
    ' in this indented ELSE section. 

  End If
  
End Sub 'NewCode

--------------------------------------------------------
Sub SheetTest (pcActivesheet as String, plSheetError as Boolean)

  If pcActiveSheet <> 'Sheet1' then
    plSheetError = 'TRUE'
  else 
    plSheetError = 'FALSE'

  End if 

Endsub

Solution

  • You have several options - first two here also suggested by @zack

    Option 1 is arguably the most common but doesn't convey anything back up the call chain

    Sub NewCode
    
        pcActiveSheet = ActiveSheet
    
        SheetTest (pcActiveSheet, plSheetError)
    
        if plSheetError = 'TRUE'
            Exit Sub
        End If
    
        ' Lots of code would go here..........
        ' If SheetError returns FALSE I know I can put the rest of code here
        ' in this indented ELSE section. 
    End Sub
    

    Option 2 Raise an error - this can convey the fact there has been an error back up the call chain. In this instance I've assumed error handling is in the calling code but you could add it into NewCode

    Sub NewCode
    
        pcActiveSheet = ActiveSheet
    
        SheetTest (pcActiveSheet, plSheetError)
    
        if plSheetError = 'TRUE'
            Err.Raise vbObjectError + 1000, "Sheet Error"
        End If
    
        ' Lots of code would go here..........
        ' If SheetError returns FALSE I know I can put the rest of code here
        ' in this indented ELSE section. 
    End Sub
    

    Option 3 Move the "lots of code" into another routine and just call that

    Sub NewCode
        pcActiveSheet = ActiveSheet
    
        SheetTest (pcActiveSheet, plSheetError)
    
        if plSheetError <> 'TRUE'
            ActionsOnNoSheetError
        End If
    End Sub
    Sub ActionsOnNoSheetError()
        ' Lots of code would go here..........
        ' If SheetError returns FALSE I know I can put the rest of code here
        ' in this indented ELSE section. 
    End Sub