Polling for Errors

This method is called polling because you check for errors after each statement. The advantage of this is that you know exactly what line of code caused the error. Listing 19.24code shows how to poll for errors.

Listing 19.24 - Polling for OLE Errors

Sub PollingDemo()
    ' (1) Start polling for errors.
    On Error Resume Next
    ' (2) This line returns error 1004 if an outline can't be created.
    oleSheet.Object.Selection.AutoOutline
    ' (3) If there was an error...
    If Err.Number Then
         ' (4) Alert user of the error.
         MsgBox "Can't create outline on this selection."
         Beep
         ' (5) Important! Reset error back to 0.
         Err.Clear
    End If
    '(6) Turn off error trapping.
    On Error GoTo 0
End Sub

The following numbered list corresponds to the comment numbers shown in Listing 19.24.

1. Turn on polling. On Error Resume Next prevents errors from halting the program; instead, Visual Basic simply assigns the error value to Err and continues to the next line of code.
2. The Excel Worksheet object's AutoOutline method does not work on all selections, and it is impossible to test the selection to see if it will work before you call AutoOutline. Your only choice is to test after - possibly causing an error.
3. If there was an error, Err.Number is set to a non-zero value (in this case 1004). This tells you that the method failed, but unless you parse the string returned by Err.Description, you can't tell what method failed. Parsing error strings is a bad idea, because they can change from version to version of Visual Basic. You're only real solution is to poll for errors after each method you think might fail.
4. Alerting the user is a good idea. Here it is done through the status bar. Using the status bar rather than a message box is less intrusive and doesn't interrupt the user's work. Be sure to clear the status bar on the next user action so the message doesn't stay up forever.
5. Clear Err. Otherwise, subsequent polling will reflect the current error value (1004) even if no error occurs.
6. Turn off polling before exiting the procedure.