Properly Handling Errors in VBA (Excel)

by

You’ve got one truly marvelous answer from ray023, but your comment that it’s probably overkill is apt. For a “lighter” version….

Block 1 is, IMHO, bad practice. As already pointed out by osknows, mixing error-handling with normal-path code is Not Good. For one thing, if a new error is thrown while there’s an Error condition in effect you will not get an opportunity to handle it (unless you’re calling from a routine that also has an error handler, where the execution will “bubble up”).

Block 2 looks like an imitation of a Try/Catch block. It should be okay, but it’s not The VBA Way. Block 3 is a variation on Block 2.

Block 4 is a bare-bones version of The VBA Way. I would strongly advise using it, or something like it, because it’s what any other VBA programmer inherting the code will expect. Let me present a small expansion, though:

Private Sub DoSomething()
On Error GoTo ErrHandler

'Dim as required

'functional code that might throw errors

ExitSub:
    'any always-execute (cleanup?) code goes here -- analagous to a Finally block.
    'don't forget to do this -- you don't want to fall into error handling when there's no error
    Exit Sub

ErrHandler:
    'can Select Case on Err.Number if there are any you want to handle specially

    'display to user
    MsgBox "Something's wrong: " & vbCrLf & Err.Description

    'or use a central DisplayErr routine, written Public in a Module
    DisplayErr Err.Number, Err.Description

    Resume ExitSub
    Resume
End Sub

Note that second Resume. This is a trick I learned recently: It will never execute in normal processing, since the Resume <label> statement will send the execution elsewhere. It can be a godsend for debugging, though. When you get an error notification, choose Debug (or press Ctl-Break, then choose Debug when you get the “Execution was interrupted” message). The next (highlighted) statement will be either the MsgBox or the following statement. Use “Set Next Statement” (Ctl-F9) to highlight the bare Resume, then press F8. This will show you exactly where the error was thrown.

As to your objection to this format “jumping around”, A) it’s what VBA programmers expect, as stated previously, & B) your routines should be short enough that it’s not far to jump.

More Related Contents:

  1. Sequence of numbers based on start and end value of 2 cells [closed]
  2. Function to convert column number to letter?
  3. Better way to find last used row
  4. 2 Dimensional array from range [closed]
  5. filter out multiple criteria using excel vba
  6. How can I create a calendar input in VBA Excel?
  7. Using Google Chrome in Selenium VBA (Installation Steps)
  8. Dir() function not working in Mac Excel 2011 VBA
  9. How to use workbook.saveas with automatic Overwrite
  10. VBA: Convert Text to Number
  11. Concatenate cell data into another data if values matches
  12. Iterating through populated rows
  13. Using VBA to detect which decimal sign the computer is using
  14. Referencing Excel Userform Control Name Value From String (VBA)
  15. Loop through each row of a range in Excel
  16. Fill formula down till last row in column
  17. Outputting Excel rows to a series of text files
  18. How to create a range from two ranges in VBA?
  19. XML parse VBA excel (function trip, & MSXML2.DOMDocument)
  20. If string contains word from list, return value adjacent to list value
  21. Refreshing all the pivot tables in my excel workbook with a macro
  22. Countif With Multiple OR Criteria
  23. Vlookup to copy color of a cell
  24. excel vba freeze pane without select
  25. Converting a string representation of a constant into a constant?
  26. VBA WinHTTP to download file from password proteced https website
  27. VBA: Modify chart data range
  28. VBA Excel simple Error Handling
  29. How to do a “Save As” in vba code, saving my current Excel workbook with datestamp?
  30. How to save semi-colon delimited csv file using VBA?

Leave a Comment