Programming Excel with VBA and .NET

Handle Exceptions

When using COM applications, exceptions are handled as trappable errors. To start detecting exceptions, use the On Error Resume Next statement. To stop detecting exceptions, use the On Error Goto 0 statement.

There are other ways to detect exceptions in Visual Basic, but the preceding technique is the most useful one when working with COM applications because the error codes generated are not very specific. You really have to know what operation was just performed in order to anticipate the exceptions that can occur—the error code tells you almost nothing.

For example, the following additions (in bold) to previous code show how to anticipate exceptions working with Word from Excel:

Sub PasteRangeToWord(  )
    Dim doc As Word.Document
    ' If a range of cells is selected.
    If TypeName(Selection) = "Range" Then
        ' Start word if it's not already running.
        If m_wd Is Nothing Then StartWord
        ' Copy the selected cells.
        Selection.Copy
        ' Detect exceptions here.
        On Error Resume Next
        ' Create a new document
        Set doc = m_wd.Documents.Add
        ' Paste the range into the Word document.
        m_wd.Selection.Paste
        If Err Then
            ' Display message
            MsgBox "Could not paste. " & _
              "Make sure Word can run and try again.", vbExclamation And vbOKOnly
            CloseWord
        End If
        On Error GoTo 0
    End If
End Sub
 
Sub CloseWord(  )
    ' Step 5: Close Word.
    On Error Resume Next
    If Not (m_wd Is Nothing) Then m_wd.Quit
    ' Set the variable to Nothing.
    Set m_wd = Nothing
    Set m_doc = Nothing
    On Error GoTo 0
End Sub

In PasteRangeToWord