Debugging Errors
There are three basic types of errors in VB programming – syntax errors, runtime errors and logic errors. Compile errors
These errors occur when you spell some words wrongly or omit a keyword. VBE detects these errors immediately, highlight the erroneous code in red and displays a warning message that this line contains a syntax error. A simple example would be omitting the keyword Then or End If from the If-Then-Else statement, omit some necessary punctuation, or use a Next statement without a corresponding For statement at design time. Below is such example.
Runtime
errors
These are
errors that occur when the code is running, and is difficult to detect
until you run your program. A run-time error results when a statement
attempts an invalid operation. The following would illustrate this type
of error. Imagine column B is ‘qty’, column C is ‘unit cost’, column D
is ‘qty’ * ‘unit cost’. An Input box asks to enter the number of rows to
do the computation, which is stored in variable nRows. If you enter a
value less than or equal to 250, the code runs without problem. If you
enter more than 250, the error dialog box as shown below will appear,
because the program will try to fill the array with more than it is
dimensioned for as in Dim myArray(250) As Integer. When you click on
Debug button, it will show you the offending line of code in yellow as
shown in the second diagram, which prompts you the clue about the error
in array.
Option Explicit
Dim myArray(250) As Integer, i As Integer, nRows As IntegernRows = InputBox("enter number of rows to do calculation")
For i = 1 To nRows
myArray(i) = Cells(i, 2) * Cells(i, 3)
Cells(i, 4) = myArray(i)
Next
A simple example to overcome the above runtime error is to re-dimension the array with REDIM and use it with or without UBound function, as illustrated in the following code. UBound returns the highest available subscript for the indicated dimension of an array. (Note: If Array has only one element, UBound returns 0. If Array has no elements, for example if it is a zero-length string, UBound returns -1)
Dim myArray() As Long, i As Long, nRows As Long
nRows = InputBox("enter number of rows to do calculation")
ReDim myArray(nRows)
For i = 1 To UBound(myArray) 'or using i = 1 To nRows
myArray(i) = Cells(i, 2) * Cells(i, 3)
Cells(i, 4) = myArray(i)
Next
Logic
errorsProgram logic error is an error in your code, however tiny it might be, that produces incorrect results. You might not even get an error message to tip you off about logic error. There is no syntactical error, your program can compile and run just as normal, but you may get a different result than you intended. A good example would be like copying data from the wrong columns. It happened because the code doesn’t check which window is active, and so it copied columns from the wrong workbook.
Below is one example that demonstrates the logic error. There is no syntax error, no runtime error, and the Average shows 11.39. Had you not counterchecked with an Excel formula which shows Average value of 13.29, you may not be aware that there was a program logic error. What went wrong? The problem was because the code counted the numbers of cells from A2 to G2. By amending the Offset position in line 3 to .Offset(0,1) will resolve the logic error.
A code that you created for Excel 2000 may not work in Excel 97, and may not work properly both in Excel 2003 and Excel 2007. The problem may lies with logic errors in the code and you have to find and fix it. IRAS runs program on our yearly tax filings, and imagine there had been logic errors, how much we had overpaid in tax? It’s scary to think of that…
Dim qohRng As Range, c As Range, sum As Single
With Range("A2")
Set qohRng = Range(.Offset(0, 0), .End(xlToRight))
End With
For Each c In qohRng
If IsNumeric(c.Value) Then
sum = sum + c.Value
End If
Next c
Range("IV2").End(xlToLeft).Offset(, 1).Value = sum / qohRng.Cells.Count
Debugging
Tools
Visual Basic cannot diagnose or fix errors for you, but it does provide debugging tools to help you analyze how execution flows from one part of the procedure to another, and how variables and property settings change as statements are executed. To display the Debug toolbar, right-click on the Visual Basic toolbar and select the Debug option, or you can choose from menu, View>Toolbars and select Debug option. Below shows these debugging tools.
 |
 |
|
 |
 |
 |
 |
 |
The following
table briefly describes each tool's purpose.
|
Debugging tool |
Purpose: |
|
Breakpoint |
Defines a line in the Code window where Visual Basic suspends execution of the application. |
|
Step Into |
Executes the next executable line of code in the application and steps into procedures. |
|
Step Over |
Executes the next executable line of code in the application without stepping into procedures. |
|
Step Out |
Executes the remainder of the current procedure and breaks at the next line in the calling procedure. |
|
Locals Window |
Displays the current value of local variables. |
|
Immediate Window |
Allows you to execute code or query values while the application is in break mode. |
|
Watch window |
Displays the values of selected expressions. |
|
Quick Watch |
Lists the current value of an expression while the application is in break mode. |
|
Call Stack |
While in break mode, presents a dialog box that shows all procedures that have been called but not yet run to completion. |
Breakpoint
You can set any number of breakpoints in your procedure. A breakpoint will allow you to track down a bug by stopping execution of code at a particular point in your procedure. Breakpoint let you run parts of your procedure that have no errors at full speed and then stop at where you suspect there might be errors. You then step through the statements until all the bugs are found. If you want to interrupt while the code is running, you would press Ctrl-Break. To go to a breakpoint, there are two ways - click in the Marginal Indicator bar to the left of the code window beside the line you want to stop execution at, or simply right-click on the line and choose Toggle>Breakpoint.
Step Over, Step Out and Run To Cursor
Break mode offers three command features to speed up stepping through your code – Step Over, Step Out and Run To Cursor. The Step Over and Step Out command are not available until you enter Break mode, for example, using Step Into command. Step Into command allows you to see exactly what each statement in your next line of code does.
Step Over command, as the word itself implies, executes the whole procedure or function called from the current procedure, instead of stepping through the called procedure statement-by-statement, as Step Into command would do. You use Step Over when you want to debug the procedure that calls another function or procedure.
Step Out command executes the remainder of the code in current procedure at full speed because you had already gotten the parts step-by-step using Step Over. Run To Cursor executes the code at full speed until it reaches the statement where your cursor is currently placed, whereupon it enters Break mode.
Locals Window
Locals window provides a quick readout of the value and type of all expressions in the active procedure via a collapsible window (see sample). The Expression column shows the name of each expression. The Value column shows the current values of the expression (including Empty if the expression is empty, or Null if a variable has no valid data, or Nothing if an object variable has disassociated itself from an actual object), and the Type column displays the data type of the expression..
Immediate
Window
You can enter lines of code you want to test in the
Immediate window,
without entering them in the procedure itself, You can display
information in the Immediate window so that you can see the values of
the variables while your procedure is running. You can use Dedug.Print
method in your procedure to print information to the Immediate window
for quick testing, or using MsgBox function (press Enter when you are
done). You can run your lines of code in the Immediate window in both
Break mode and Design mode.
Watch
Window
When you enter Break mode (example, by pressing F8), you can use
Watch window to
track the running display of the values of the variables and expressions as each
statement in the code executes. This information allows you to pinpoint where
the unexpected values for a variable or expression occur as your code is
running. As VBE stores watch expressions during the current editing session, so
you can move from procedure to procedure without losing your watch expressions.
Before you can display a variable in the Watch window, you must declare it; otherwise VBE will show an error: “variable not created in this context”. If a variable or expression listed in the Watch window has not been initialised, you will see the display “<Out of Context>” in the Value column, and “Empty” in the Type column.
Right-click the variable or expression in your code, choose Add Watch from the shortcut menu. In the Add Watch dialog box, select one of the three options in the Watch Type group to define how the system responds to the watch expression. If you right-click on the watch expression in the Watch window, you can choose Edit Watch.
: “Watch Expression” - it displays the value of the watch expression.
: “Break When Value is True” - it stops execution if the expression evaluates to True (a non-zero value). In another words, it allows you to run your code without stepping through each statement that doesn’t change the value of the watch expression to True.
: “Break When Value Changes” - it stops execution when the value of the expression changes. That means it allows you to run your code and stop with each change of the value.
Quick
Watch
feature
Call Stack dialog box
Only in Break mode you can use the Call Stack dialog box to display a list of active procedure calls. From the View menu, choose Call Stack. When you start to run a procedure, that procedure is added to the Stack Call list. That procedure then calls another procedure, the name of the second procedure is added to the Call Stack list again. When the called procedure stops executing, it is then removed from the Call Stack list. This way you are able to see what procedures are being called by another procedure.
Runtime
Errors
Handlers
Sometimes you can anticipate certain runtime errors that are likely to
occur and that can be trapped. Trapping an error means catching it so
that you can take action about it. For example, an error occurs when you
try to create a spreadsheet that is already exist, or you try to delete
or move a file that had already been removed, or you try to open a file
that is currently in use by another user. VBA allows you to write error
handlers that can trap runtime errors. To trap an error, you use the On
Error statement. You have to make sure your error-handler does not run
when no error has occurred. To do this, you use Exit Sub statement.
On
Error
GoTo
In the example below, the GoTo statement directs execution to a label beyond the error-handler. The Resume statement resumes execution of the procedure after trapping a error with the error-handler.
On Error GoTo errorhandler1
Workbooks.Open Filename:="C:\FY07_UMC_week10.xls"
On Error GoTo errorhandler2
Sheets(1).Rows("1:1").Find(What:="PartNumber", SearchDirection:=xlNext).Activate
Exit Sub
errorhandler1:
If Err = 1004 Then
MsgBox "encountered runtime error" & Err & ". File doesn't exist"
Resume
End If
errorhandler2:
MsgBox "can not find the header name 'Material' in Row 1"
On Error Resume Next
statementThis statement is useful if you want to suppress an unimportant error from displaying. The On Error Resume Next statement simply tells us, “if an error is encountered, ignore it and go on to the next statement”. In the example code below, assuming the sheet “week3data” does not exist, then no error message will be displayed, and the code will execute the message box statement ‘can not delete a non-existing sheet - error code 9’. Because each specific type of error will have a particular error code in VBA, and there are almost endless of them, so don’t bother to learn all these error codes. As Err is non-zero, and if you would notice the line ‘If Err <> 0 Then’, this simply means and if an error occurs in your code…then..; otherwise Err is 0.
On Error Resume Next
Application.DisplayAlert = False
Worksheets("week10").Delete
If Err <> 0 Then
MsgBox "can not delete a non-existing sheet - error code " & Err
End If
On Error GoTo 0
Error handler works only for the procedure in which it is in, and it is disabled by VBA when the code has finished executing. However, you can also disable an error trap before the end of the procedure by using the statement On Error GoTo 0
Dim tableSEL As Range
On Error Resume Next
'
enable error handler
With Selection
If .Cells.Count > 1 Then
' number of cells count
Set tableSEL = Selection
Else
Set tableSEL = .CurrentRegion
On Error GoTo 0
' disable
error
handler
End If
End With
Handling user interrupts
I
Application. EnableCancelKey = xlDisabled
You can re-enable the user input when the procedure is running by setting EnableCancelKey property to xlInterrupt
Application.EnableCancelKey = xlInterrupt
You can use xlErrorHandler to trap Ctrl+Break keystroke as error 18. See
example below.
Dim i As Long
On Error GoTo errorhandler
Application.EnableCancelKey = xlErrorHandler
For i = 1 To 1000 'or using i = 1 To nRows
Cells(i, 4) = Cells(i, 2) * Cells(i, 3)
Next i
errohandler:
If Err.Number = 18 Then
If MsgBox("do you really want to stop procedure running?", _
vbYesNo + vbCritical, "user interrupted") = vbNo Then
Resume
Else: End
End If
End If