Debugging in Excel VBA

Debugging Input Errors

For input errors, adding the Option Explicit statement at the beginning of the module can solve the problem. The Option Explicit statement performs type checking on all variables in the module, and variables with input errors will be considered undefined. For example, add this statement in the first line of the form module in the sample program. The sample file path is Samples\ch12\Excel VBA\InputError.xlsm.

Option Explicit
Private mdblDate1 As Double
Private mdlbDate2 As Double

If mdblDate1 is misspelled as mdblDat1 in the Test procedure, the error message displayed when running the program is shown in Figure 12-1.

Sub Test()
    mdblDat1 = 0
    mdblDate2 = 0
End Sub

Debugging Runtime Errors

Runtime errors refer to errors that occur during program execution and can be avoided by writing error-tolerant statements. Objects and statements such as Err, Error, On Error, and Resume provided by Excel VBA can prevent runtime errors.

The following program first uses the On Error statement to declare that if an error is found, it will jump to the line labeled X. Then, the Raise method of the Err object is used to generate an error. Because an error is generated, the program jumps to line X, outputs the error information in the Immediate Window, executes the next line, and the Resume Next statement continues execution from the line after the error occurred, outputting information in the Immediate Window. The sample file path is Samples\ch12\Excel VBA\RuntimeError.xlsm.

Sub Test()
    On Error GoTo X
    Err.Raise 1
    Debug.Print "Now restarting"
    Exit Sub
X:  Debug.Print "Err="; Err.Description
    Resume Next
End Sub

Running the procedure displays the results in the Immediate Window, as shown in Figure 12-2.

Figure 12-2 Runtime Error

Immediate Window

Application-defined or object-defined error
Err=
Now restarting

Debugging Logical Errors

When a logical error occurs, the program does not report an error but produces incorrect results. Since there is no error prompt, debugging logical errors is more difficult than debugging input errors and runtime errors.

For logical errors, using options in the Debug menu of the VB project can achieve good debugging results. The Debug menu is shown in Figure 12-3. Main debugging methods include step-by-step debugging, watch debugging, and breakpoint debugging. As an example program for debugging, add the following code to a new module. The sample file path is Samples\ch12\Excel VBA\LogicalError.xlsm.

Function Square(dblA As Double) As Double
    ' Calculate the square of a given number
    Square = dblA * dblA
End Function
Sub Sum()
    Dim dblData1 As Double
    Dim dblData2 As Double
    dblData1 = 12
    dblData2 = Square(3)
    Debug.Print dblData1 + dblData2
End Sub

Step-by-step debugging includes methods such as Step Into, Step Over, and Run to Cursor, which are commonly used. Step-by-step debugging can quickly locate potentially problematic code blocks and focus on troubleshooting.

Watch debugging monitors the execution of selected code using expressions. Select Debug → Add Watch to open the Add Watch dialog box, as shown in Figure 12-4. Enter dblData2 = 9 in the Expression text box, select the Break when value is True radio button in the Watch Type group, and click OK. The Watch Window panel is displayed, as shown in Figure 12-5. During program execution, execution pauses when dblData2 = 9.

Breakpoint debugging requires setting breakpoints. When the program runs, it stops at the breakpoint. As shown in Figure 12-6, clicking in the gray area before the specified line sets a breakpoint. When the program runs, it stops at this line. At this point, hovering the mouse pointer over a variable displays its value, and the Immediate Window can also be used to check the value of the currently specified expression. As shown in Figure 12-6, entering ?dblData2 in the Immediate Window and pressing Enter displays the value of variable dblData2 as 9.