Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

I have looked online as much as I could (except for the Microsoft support website, which is blocked at work for some reason). I am trying to simply skip an error. My code written here is simplified but should work the same way.

What my code is supposed to do: One of my subs creates shapes in a loop and names them (btn_1, btn_2, etc). But before creating them, it calls a sub that tries to delete them so as not to create duplicates. This sub loops through (btn_1, btn_2, etc) and deletes the shapes using:

for i = 1 to (a certain number)
    Set shp = f_overview.Shapes("btn_" & i)
    shp.delete
next

Of course, it happens that the shape cannot be deleted because it simply does not exist. 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. I have tried it inside the loop, before the loop, etc, like so:

for i = 1 to (a certain number)
    On Error Resume Next
    Set shp = f_overview.Shapes("btn_" & i)
    shp.delete
next

As far as I understand it is supposed to loop right through if the shape doesn't exist, but I still get the same error whether or not I add the On error resume next! What am I doing wrong?

EDIT: There is no error when the shapes do exist.

share|improve this question

6 Answers 6

up vote 3 down vote accepted

It sounds like you have the wrong error trapping option set. Within the VBA Editor, Select Tools -> Options. In the window that opens, select the General tab, and pick the Break on Unhandled Errors radio button. This should allow Excel to properly process the On Error Resume Next command.

I suspect that you have Break on All Errors selected.

share|improve this answer
    
Thanks! I kept trying the suggestions of the other answerers and all failed. This was why although they also provided valuable information! –  David GM 2 hours ago
    
@DavidGrand'Maison I agree that the error handling being done in the other answers is cleaner and better. But your original code should have worked. –  Degustaf 2 hours ago

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
share|improve this answer
1  
++ on Proper Error Handling :) –  Siddharth Rout 3 hours ago
    
@SiddharthRout yeah, except I didn't test it and now the more I think of it, the more I believe the assignment would throw an index out of range error, in which case FreeMan's answer has a better solution. –  Mat's Mug 2 hours ago
    
That;s ok :) Edit your above code. My comment was the proper error handling that you showed t the bottom of the post. i did mention about that in my post as well :) –  Siddharth Rout 2 hours ago
    
@SiddharthRout edited, ...without stealing from anyone's answer :) –  Mat's Mug 2 hours ago
    
Good tips, for someone new to error handling like me. The reason it was failing, though, was apparently because I had the wrong settings (see chosen answer). –  David GM 2 hours ago

There is nothing WRONG in using OERN (On Error Resume Next) provided you understand what you are doing and how it is going to affect your code.

In your case it is perfectly normal to use OERN

Dim shp As Shape

For i = 1 To (a certain number)
    On Error Resume Next
    Set shp = f_overview.Shapes("btn_" & i)
    shp.Delete
    On Error GoTo 0
Next

At the same time ensure that you don't do something like

On Error Resume Next
<Your Entire Procedure>
On Error GoTo 0

This will suppress ALL errors. Use proper error handling as shown by Matt

Edit:

Here is another beautiful example on how to use OERN This function checks if a particular worksheet exists or not.

Function DoesWSExist(wsName As String) As Boolean
    Dim ws As Worksheet

    On Error Resume Next
    Set ws = ThisWorkbook.Sheets(wsName)
    On Error GoTo 0

    If Not ws Is Nothing Then DoesWSExist = True
End Function

If you wish you can also loop through all the sheets to check is the sheet exists or not!

share|improve this answer
    
Thanks for the explanation! The reason it was failing, though, was because I had the wrong settings (see chosen answer). –  David GM 2 hours ago
1  
++ but unfortunately most don't understand the implications of using OERN. I like that acronym btw. I'm keeping it. –  RubberDuck 59 mins ago

Instead of trying to blindly delete shapes and skipping errors, why not run through the list of known shapes and delete them. Then you don't have to worry about an On Error Resume Next which often ends up being abused.

Sub Test(TheSheet As Worksheet)

Dim Shp as Shape

For Each Shp in TheSheet.Shapes
  If left(Shp.Name, 4) = "btn_" Then
    Shp.Delete
  End if
Next

End Sub

If you want to delete all shapes, remove the If statement. If you want to delete a number of differently named shapes, modify the If statement appropriately.

share|improve this answer
    
++ Another good way of deleting the shapes :) –  Siddharth Rout 3 hours ago
    
AH excellent! This is the way I will choose to do it. Thank you for the fix. +1 for answer even though you don't explain how to use error handling :-) –  David GM 2 hours ago
    
You are correct on the lack of error handling detail - this loop doesn't require it, especially not in the way you were thinking. Mat's Mug does a fine job of covering error handling in general. –  FreeMan 2 hours ago

Try

On Error Resume Next

for i = 1 to (a certain number)
    Set shp = f_overview.Shapes("btn_" & i)
    if err<>0 then err.clear else shp.delete
next

on Error Goto 0
share|improve this answer
Sub test()
On Error Resume Next
Application.DisplayAlerts = False

For i = 1 To 5
    Set shp = f_overview.Shapes("btn_" & i)
    shp.Delete
Next
Application.DisplayAlerts = True
End Sub
share|improve this answer
1  
--- Very Bad Idea –  Siddharth Rout 3 hours ago

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.