VBA subfunctions Error handling

Question

I have a neat example I need help with. I am trying to find a way to handle my errors but keep my subfunctions modular(not needing to know about the rest of my code to work properly)

In Excel VBA, there's no Try/Catch functionality. There are native functions in VBA that return an error if they fail. I am looking to make the following example neat

Function wrapperForFunction(input As Variant) As Boolean
  On Error goto returnTrue
  fancyFunctionThatReturnsError(input As Variant)
  wrapperForFunction = False
  LINE OF CODE THAT SETS ERROR BACK TO WHAT IT WAS BEFORE CHANGING IT TO returnTrue
  Return
returnTrue:
  wrapperForFunction = True
  LINE OF CODE THAT SETS ERROR BACK TO WHAT IT WAS BEFORE CHANGING IT TO returnTrue
End Function

Sub MainProgram()
  On Error goto errorHandlerMain
  '' do stuff
  if wrapperForFunction(input) then 'do stuff
  '' do morestuff
  if wrapperForFunction(input) then 'do stuff
errorHandlerMain:
  'non-erroring cleanup code
End Sub

Sub NestedSub()
  On Error goto errorHandlerNestedSub
  '' do stuff
  if wrapperForFunction(input) then 'do stuff
errorHandlerNestedSub:
  'non-erroring cleanup code      
End Sub

What I am trying to avoid is this kind of solution

Function wrapperForFunction(input As Variant) As Boolean
  On Error goto returnTrue
  fancyFunctionThatReturnsError(input As Variant)
  wrapperForFunction = False
  Return
returnTrue:
  wrapperForFunction = True
End Function

Sub MainProgram()
  On Error goto errorHandlerMain
  '' do stuff
  tmp = wrapperForFunction(input) ' <------------ THIS LINE WAS ADDED 
  On Error goto errorHandlerMain   ' <------------ THIS LINE WAS ADDED 
  if tmp then 'do stuff
  '' do morestuff
  tmp = wrapperForFunction(input) ' <------------ THIS LINE WAS ADDED 
  On Error goto errorHandlerMain   ' <------------ THIS LINE WAS ADDED 
  if tmp then 'do stuff
errorHandlerMain:
  'non-erroring cleanup code
End Sub

Sub NestedSub()
  On Error goto errorHandlerNestedSub
  '' do stuff
  tmp = wrapperForFunction(input) ' <------------ THIS LINE WAS ADDED 
  On Error goto errorHandlerNestedSub   ' <------------ THIS LINE WAS ADDED 
  if tmp then 'do stuff
errorHandlerNestedSub:
  'non-erroring cleanup code
End Sub

Any suggestions?? Is there a way to find out what the current "On Error goto x" state is and reset it back to what it was before you changed it? Like in

Function foobar()
  Dim errorLine As Long: errorLine = CURRENT_ERROR_LINE
  On Error goto 40
  'do stuff
  On Error goto errorLine
  Return
40'cleanup code

End Function

You can simulate a TRY CATCH block in VBA Please see here stackoverflow.com/q/30991653/4413676 — HarveyFrench
– HarveyFrench, Commented Jun 22, 2015 at 23:46

Aiken · Accepted Answer · 2014-07-01 08:10:18Z

A couple of points to note about Error Handling in VBA:

The Scope of an On Error Goto <x> statement is the procedure in which the statement is contained and any procedures called from within that procedure.
As an extension to the previous point: If an error occurs it will 'bubble' up the call stack until it reaches a procedure in which error handling is defined, if no procedure on the stack has defined error handling it will display a generic message box.

With the above behaviour in mind it's not necessary to re-state On Error Goto <x> after each call to a procedure as any change the called procedure makes to its error handling will have gone out of scope by the time it has completed.

Working with the following example:

Public Sub Subroutine1()
    Dim a As Double, b As Double, c As Double

    On Error GoTo Err_Subroutine
    a = 10
    b = 2

    c = Divide(a, b)
    c = c / 0        

Exit_Subroutine:
    Exit Sub

Err_Subroutine:
    Select Case Err.Number
        Case 11 'DIV/0
            MsgBox "DIV/0 error in Sub Subroutine1()"
            Resume Exit_Subroutine
        Case Else
            MsgBox "Unhandled error in Subroutine1()"
            Resume Exit_Subroutine
    End Select
End Sub

'Returns a/b or 0 if an error occurs
Public Function Divide(a, b)
    On Error GoTo Err_Divide

    Divide = a / b

Exit_Divide:
    Exit Function

Err_Divide:
    Select Case Err.Number
        Case 11 'DIV/0
            MsgBox "DIV/0 error in Function Divide()"
            Divide = 0
            Resume Exit_Divide
        Case Else
            MsgBox "Unhandled error in Function Divide()"
            Divide = 0
            Resume Exit_Divide
    End Select
End Function

Despite the fact that Divide has been called and the statement On Error GoTo Err_Divide executed, when the DIV/0 error occurs on the following line the error will still be directed to Err_Subroutine.

Might also be worth noting that On Error GoTo 0 returns error handling to its default state i.e. unhandled within the current scope.

Collectives™ on Stack Overflow

VBA subfunctions Error handling

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related