Programming Excel with VBA and .NET

Debug Excel .NET Applications

Excel projects do not report errors that occur in Excel the way you might expect. Instead of halting execution when an error occurs, Excel projects just continue on as if nothing happened. This can be very confusing since the code exits the procedure where the error occurred and no warning is displayed. A good way to see this behavior is to try to activate a worksheet that doesn’t exist. For example:

Private Sub ThisWorkbook_Open(  ) Handles ThisWorkbook.Open
    ThisApplication.Sheets("doesn't exist").activate(  ) ' Error! Code exits here.
    ' Set the ActiveSheet object
    If ThisApplication.ActiveSheet.Type = Excel.XlSheetType.xlWorksheet Then _
    ActiveWorksheet = CType(ThisApplication.ActiveSheet, Excel.Worksheet)
    ' Find the control on the sheet and hook up its events.
    cmdReformat = CType(FindControl("cmdReformat"), MSForms.CommandButton)
End Sub

In the preceding code, ActiveWorksheet and cmdReformat are never set because Excel can’t find the worksheet to activate. The project keeps running, though, and you’re just left to wonder why none of your event procedures are working.

You can prevent this by telling Visual Studio .NET to break into the debugger when exceptions are thrown, as described in the following steps:

1. From the Debug menu, choose Exceptions. Visual Studio .NET displays the Exceptions dialog box.

2. Select Common Language Runtime Exceptions and under “When the exception is thrown,” select “Break into the debugger,” as shown in Figure 25-17. Then click ...