|
5.15. Debug Excel .NET ApplicationsExcel 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:
Figure 5-20. Set Break into debugger to detect exceptions in Excel projectsOnce 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 startYou 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. |
|