Programming Excel with VBA and .NET

Exceptions

Exceptions are runtime errors that you anticipate. Generally, exceptions occur as the result of using resources outside of Excel, such as trying to get a file from disk or trying to connect to a database. It’s very difficult to anticipate all the things that could go wrong and unnecessarily complex to try to write code for situations that may occur only rarely. That’s what exception handling is for.

In Visual Basic, you turn on exception handling using the On Error statement. On Error tells Visual Basic to watch for exceptions and provides instructions on what to do if one happens. For example, the following code starts Notepad from Excel; if Notepad isn’t installed or can’t be found, Excel displays a warning message:

    Sub ShowNotepad( )
        On Error Resume Next
        Shell "notepad.exe", vbNormalFocus
        If Err Then MsgBox "Notepad could not be found.", , "Warning"
    End Sub

This style of exception handling is called inline because On Error Resume Next tells Visual Basic to execute the rest of the procedure one line at a time even if a problem occurs. With this technique you typically test the value of Err after each line that you think might cause an exception.

Err returns an Error object if an error occurred. The Error object’s default property is the error code number, and the preceding code simply tests if that value is not zero (False). In some cases you may want to test the value within a Select Case statement. On Error provides an alternate syntax that causes any error to ...