"catch" error due to missing privileges?

Question

I have the following code inside a VBA-Script (inside an Excel[openxml]-Sheet):

  For Each Co In ThisWorkbook.Worksheets("META").ChartObjects
        Co.Delete
    Next Co

When I open the Excel File the first time "Co.Delete" throws an "Access Denied"-Error ("You do not have enough privileges to complete this operation")

When I select another sheet and go back to the first sheet and THEN clicke the SAME button like before, calling the method this error is NOT thrown. It looks quite like a Excel-Bug to me.

I know there is no try...catch in VBA and I know I could just insert "on error resume next" but I just don't want to :)

Is there a different approach like a method "if (Co.DeletionAllowed) Then" ?

`On Error Resume Next` + `If Err.Number<>0` *IS* the equivalent of try-catch in VBA. Why don't you want to use it? Ok, it isn't nearly as elegant, but it does the same thing. Can also use `On Error GoTo ErrorHandler` with an `ErrorHandler:` block at the end of your procedure. — Jean-François Corbett, Jun 13 '14 at 12:33
My point was more about being able to prevent the error from even happening. Would do the same on .net (where possible). I prefer to use try...catch only on unexpected errors. — Ole Albers, Jun 13 '14 at 13:56
Ah, I see. You are right; uncritically using `On Error Resume Next` on its own to swallow all errors indiscriminately is indeed bad practice. — Jean-François Corbett, Jun 13 '14 at 14:25

score 1 · Accepted Answer · edited May 23 '17 at 10:33

AFAIK there is no way to detect ahead of time whether you will get this error.

This would be the way to catch and handle the error:

    On Error GoTo ErrorHandler
    For Each Co In ThisWorkbook.Worksheets("META").ChartObjects
        Co.Delete
    Next Co

    'rest of your procedure goes here...

    'Now here comes the bit that does the error handling -- equivalent to a catch block
ExitProcedure:
    On Error Resume Next
    'Clean-up code goes here
    Exit Sub
ErrorHandler:
    Select Case Err.Number
    Case 12345 ' or whatever error number you are getting
        'Permission denied when deleting ChartObject. Ignore and skip to next statement.
        Resume Next ' or whatever else you want to do
    Case Else
        MsgBox "Unexpected error! See Immediate window for detail.s"
        Debug.Print Err.Number, Err.Source & ", Procedure lkjh of Module Module1", Err.Description, Err.HelpFile, Err.HelpContext
        'Or whatever else you want to do to log unexpected errors.
    End Select
    Resume ExitProcedure
    Resume

Why that second Resume?

"catch" error due to missing privileges?

1 Answers1