Error Handling Question

[AutoFilterQueen]

I have recently had 3 instances where my error handling is not working the way I had intended. I am not sure whether these instances are related or three different problems. I have used error handling many times before and it has worked the way I intended it, so I am hoping this is not just my complete lack of understanding the concept!!! I am currently running Excel 2007 but had previously used this code with Excel 2003 and it worked the way I had intended with Excel 2003.

Instance 1:

Public Sub ClearClipboard()
    On Error Resume Next
    Application.CommandBars("Clipboard").FindControl(, 3634).Execute
    On Error GoTo 0
End Sub

This subroutine produces the error message “Run-time error '91': Object variable or With block variable not set” and the offending line of code is the one sandwiched between the error handling lines. This subroutine did not produce an error in Excel 2003 (which may or may not be relevant). I have since found a better way to programmatically clear the clipboard (http://www.cpearson.com/excel/Clipboard.aspx) but I still wonder why the On Error Resume Next does not result in ignoring the error in this line of code.

Instance 2:

On Error GoTo nonefound
comparesheet.Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
            xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
            , SearchFormat:=True).Activate
On Error GoTo 0

Once again, this code produces the error message “Run-time error '91': Object variable or With block variable not set” and the offending line of code is the one sandwiched between the error handling lines. In Excel 2003, this did go to nonefound.

Instance 3:

On Error Resume Next
activefile.Sheets("Raw Data").ShowAllData
On Error GoTo 0

This code produces the error message Run-time error '1004': ShowAllData method of Worksheet class failed and the offending line of code is the one sandwiched between the error handling lines. In Excel 2003, if the ShowAllData method failed it ignored the error and proceeded to the next line of code. Is there some difference between error handling in Excel 2003 and Excel 2007? I really need to make error handling work for me in instances 2 and 3. I was able to get around it in instance 1 by finding another way to clear the clipboard, but in instances 2 and 3 I expect there to be errors and for good reason, and I want to handle them appropriately. Any insight would be greatly appreciated!

 

[Norie]

Not sure about 1, always thought command bars had been replaced by the ribbon.

You could try clearing the clipboard like this though.

Application.CutCopyMode = False

2 Without On Error perhaps?

Set rngFnd = comparesheet.Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
            xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
            , SearchFormat:=True)
' check if found
If Not rngFnd Is Nothing Then
     ' found
    rngFnd.Activate
Else
    ' do whatever you need to do if not found
End If

3 Dittoish

If Not ThisWorkbook.Sheets("Raw Data").AutoFilter Is Nothing Then
   ThisWorkbook.Sheets("Raw Data").ShowAllData
End If

 

[AutoFilterQueen]

Still Confused about Error Handling

Norie: Thanks for your reply! Sorry for the long delay but things have been hectic here. The code you suggested worked great for instance 2. The code you suggested did not work for me in instance 3, but I found a solution in BatmanUK's post in this thread: http://www.mrexcel.com/forum/showthread.php?t=228818 .

So, now that the immediate problem is solved and my code works, I'm still confused on the concept of error handling and why it didn't work (the way I expected) in these instances. I thought that if I placed a line of code after On Error Resume Next that Excel would ignore any errors generated until another On Error statement was encountered. Why didn't Excel ignore the errors in instances 1 and 3? I thought that if I placed a line of code after On Error Goto Label that if the code generated an error, Excel would go to the Label instead of showing the error message. Why didn't Excel do that for instance 2? I just want to make sure that I understand the concept now because I thought I did until I encountered these 3 instances.

 

[Norie]

Re: Still Confused about Error Handling

I don't really know much about error handling in VBA, tend to avoid it if possible.

It's actually this type of thing happening that puts me off.

If the code has more than one error handler perhaps they are overlapping in some way?

Hard to tell without seeing all the code.

 

[AutoFilterQueen]

Re: Still Confused about Error Handling

Trust me, you REALLY don't want to see all the code... 2765 lines so far! That's a good thought about more than one error handler overlapping in some way. Maybe the best thing to learn from this is whenever possible to write my code in such a way to avoid error handling! Thanks for your help and ideas.

 

[AutoFilterQueen]

Well, this has been in the back of my mind bugging me for the past 5 1/2 months. Today, I finally figured it out. The cause of the problem was that I had the "Break on All Errors" setting selected as the error trapping option in the Visual Basic Editor. I fixed it by clicking Tools > Options > General Tab > and selecting "Break on Unhandled Errors". Now my error handling is working the way I had originally expected it would.

My concern now is that other users of my file may also have this setting incorrect. Unlike Access, Excel VBA does not have an elegant way to programmatically set the error trapping option. I guess I will use the Application.Sendkeys method. Here is a thread on another forum about this: http://www.excelforum.com/excel-programming/738502-using-vba-to-set-error-trapping-status-excel.html. I just wanted to post here the answer that I finally found in case anyone else experiences the same problem.

 

[Norie]

Are you sure other people will have that setting?

Is there some reason why they might have changed it from 'Break on Unhandled Errors', which is the default settting?

 

[AutoFilterQueen]

I hope no one else would have that setting. But then again, I have no idea why I had that setting in the first place. I figure if I had it, someone else might. Or maybe I should just assume no one else would and keep in mind if they call me with a problem that is one thing to check. I'm sending this to other companies so I try to account for everything that *might* happen but that probably isn't possible anyway.

 

[Norie]

I suppose the ideal solution would be to make sure the code doesn't cause any errors so you wouldn't need any error handling.

What code, if any, is still giving you errors?