Results 1 to 2 of 2

Thread: [RESOLVED] Error handling in VBA

  1. #1

    Thread Starter
    New Member
    Join Date
    Feb 2012
    Posts
    5

    Resolved [RESOLVED] Error handling in VBA

    Hi all,


    I need to know something about error handling. Suppose I have various modules in VBA. Suppose in Module1 I have a sub that controls the flow of my program. Suppose this sub looks something like:

    Code:
    Sub MainCode()
    
      On Error GoTo ErrHandler
    
      Call ImportData
    
      Call CalcResult
    
    ExitPoint:
        Exit Sub
         
    ErrHandler:
        Call ErrorHandler(Err.Number, Err.Description, "Error in main")
    
    End Sub
    The subroutines ImportData and CalcResult are defined in a different module, say Module2.

    Simply, they look something like:

    Code:
    Sub ImportData()
    
      On Error GoTo ErrHandler
    
      ' Do something
    
    ExitPoint:
        Exit Sub
         
    ErrHandler:
        Call ErrorHandler(Err.Number, Err.Description, "Error while importing data")
    
    End Sub
    and

    Code:
    Sub CalcResult()
    
      On Error GoTo ErrHandler
    
      ' Do something
    
    ExitPoint:
        Exit Sub
         
    ErrHandler:
        Call ErrorHandler(Err.Number, Err.Description, "Error while calculating results")
    
    End Sub
    Now, what I would like to achieve is that if the subroutine ImportData throws an error, the main subroutine (MainCode) stops doing its thing. Right now, I get some message box where I can click on OK, and then it goes straight into the CalcResult subroutine. Obviously, CalcResult can't do it's thing since it is missing data. How can I handle this code the way I want?

    For your information, ErrorHandler is defined in another module and looks something like:

    Code:
    Function ErrorHandler(errNumber As Integer, errDescription As String, procedureName As String) As Boolean
        Dim errMessage As String: errMessage = "Error Number: " & errNumber & vbCrLf & errDescription & vbCrLf & procedureName
        ErrorHandler = (MsgBox(errMessage, vbExclamation, "Error") = vbCancel)
    End Function
    Thanks.

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Error handling in VBA

    The simplest solution is to alter the ImportData Sub so that it is a Function instead, so that it can return a value saying whether it was successful or not, eg:
    Code:
    Function ImportData() as Boolean
    
      On Error GoTo ErrHandler
    
      ' Do something
    
    ExitPoint:
        ImportData = True 
        Exit Sub
         
    ErrHandler:
        Call ErrorHandler(Err.Number, Err.Description, "Error while importing data")
        ImportData = False 
    End Function
    (the line ImportData = False isn't actually needed [the default value is False], but it helps clarity)

    Then change the call from this:
    Code:
      Call ImportData
    to this:
    Code:
      If ImportData = False Then Exit Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width