Section 5.15.Debug Excel .NET Applications


5.15. 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.     ' Set the ActiveSheet object     If ThisApplication.ActiveSheet.Type = Excel.XlSheetType.xlWorksheet Then         ActiveWorksheet = CType(ThisApplication.ActiveSheet, Excel.Worksheet)     Endif     ' 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 select When the exception is thrown: Break into the debugger, as shown in Figure 5-20, then click OK.

Figure 5-20. Set Break into debugger to detect exceptions in Excel projects


Once you tell Visual Studio .NET to break on all runtime exceptions, you'll start seeing exceptions that are handled as well those that aren't. Two handled file-not-found exceptions occur every time an Excel project starts, as shown in Figure 5-21.

Figure 5-21. This (handled) exception occurs twice every time an Excel project start


You can ignore these handled exceptionsclicking Continue a couple of times each time you start is a little annoying, but at least you can catch code that doesn't work!

Another way to detect exceptions, without breaking for all of them, is to add Try...Catch blocks to your code. The FindControl method actually does this, but it omits a useful technique for reporting exceptions while debugging. It's a good idea to add a Debug.Fail statement to the code template's FindControl method as shown here (in bold ):

    Overloads Function FindControl(ByVal name As String, _      ByVal sheet As Excel.Worksheet) As Object        Dim theObject As Excel.OLEObject        Try            theObject = CType(sheet.OLEObjects(name), Excel.OLEObject)            Return theObject.Object        Catch Ex As Exception            ' Report the exception.             Debug.Fail(Ex.Message, Ex.ToString)        End Try        Return Nothing    End Function

Now, FindControl displays an error message during debugging if a control is not found, rather than just continuing on. Debug.Fail is especially useful since it doesn't affect your released applicationthe .NET Framework disables the Debug class in code that is built for release.


Tip: Excel automatically loads the assembly associated with a workbook when you open that workbook. If the assembly has errors, or if you just want to bypass loading the assembly, hold down the Shift key while opening the workbook. That prevents Excel from running the start-up code and loading the assembly.



    Excel 2003 Programming. A Developer's Notebook
    Excel 2003 Programming: A Developers Notebook (Developers Notebook)
    ISBN: 0596007671
    EAN: 2147483647
    Year: 2004
    Pages: 133
    Authors: Jeff Webb

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net