VBA Excel simple Error Handling

by

I have found that most of the time, the reccomended fix is to add (on error resume next) before setting the shape, as I get an error saying it does not exist.

NO!

The recommended way to handle runtime errors is not to shove them under the carpet and continue execution as if nothing happened – which is exactly what On Error Resume Next does.

The simplest way to avoid runtime errors is to check for error conditions, and avoid executing code that results in 100% failure rate, like trying to run a method on an object reference that’s Nothing:

For i = 1 To (a certain number)
    Set shp = f_overview.Shapes("btn_" & i)
    If Not shp Is Nothing Then shp.Delete
Next

In cases where you can’t check for error conditions and must handle errors, the recommended way is to handle them:

Private Sub DoSomething()
    On Error GoTo CleanFail

    '...code...

CleanExit:
    'cleanup code here
    Exit Sub

CleanFail:
    If Err.Number = 9 Then 'subscript out of range
        Err.Clear
        Resume Next
    Else
        MsgBox Err.Description
        Resume CleanExit
    End If
End Sub

More Related Contents:

  1. Handle cancellation of InputBox to select range
  2. Update an Excel table with new values from another table
  3. we want to search for an empty column and add items to that column using a vba form
  4. VBA code – Sumbycellscolor
  5. Excel VBA, getting range from an inactive sheet
  6. Is there a way to crack the password on an Excel VBA Project?
  7. How do I declare a global variable in VBA?
  8. How to pause for specific amount of time? (Excel/VBA)
  9. VBA code doesn’t run when cell is changed by a formula
  10. Easiest way to loop through a filtered list with VBA?
  11. Quicker way to get all unique values of a column in VBA?
  12. How to set recurring schedule for xlsm file using Windows Task Scheduler
  13. Parsing an ISO8601 date/time (including TimeZone) in Excel
  14. Getting the actual usedrange
  15. Delete a row in Excel VBA
  16. Concatenate cell data into another data if values matches
  17. Iterating through populated rows
  18. How can I run VBA code each time a cell gets its value changed by a formula?
  19. How to convert 32 bit VBA code into 64 bit VBA code
  20. Loop through each row of a range in Excel
  21. Outputting Excel rows to a series of text files
  22. How to create a range from two ranges in VBA?
  23. XML parse VBA excel (function trip, & MSXML2.DOMDocument)
  24. Vlookup to copy color of a cell
  25. every possible combination of the contents of 2 columns in excel
  26. How to delete rows in Excel based on criteria using VBA?
  27. Converting a string representation of a constant into a constant?
  28. Macro to export MS Word tables to Excel sheets
  29. How to protect Excel workbook using VBA?
  30. How to get the range of the visible rows after applying an advanced filter in Excel (VBA)

Leave a Comment