Debugging and Error Handling

Debugging is the process of removing programming errors (bugs) in the code of our macros. There are basically four types of errors:

Language/syntax errors are typos or missing components in the VBA code (e.g. you type Rage instead of Range, miss a dot or parenthesis, etc.).

Compile errors occur when VBA cannot understand a statement correctly, usually due to some incorrect code instructions/expressions (or a language/syntax error).

In some cases, VBA highlights those errors in red when moving to the next line, and if Auto Syntax Check is turned on, it also displays a message box with the type of error (see the example below). The Auto Syntax Check status can be changed under Tools -> Options -> Code Settings in the VBA editor. In some other cases, the compile error prompts when attempting to run the macro.

Runtime errors occur during macro code execution due to VBA instructions impossible to run such as selecting a worksheet that does not exist, or trying to open a workbook that is already open or that cannot be found under the given path, etc. This type of errors must be predicted and anticipated in the code through error handling (see later).

In the example above, a runtime error was caused by the last line of code, where the Value property has not been specified. Therefore, VBA interprets is the wrong property for the Range object and triggers and error. The correct code would be:

 

 ActiveSheet.Range("B1").Value = uDept

Program logic errors do not necessarily generate any error message but nevertheless render erroneous results due to misleading or not well defined instructions such as not fully qualified or correct object referencing, or incorrect condition statements, among other examples.

VBA Debugging Tools

VBA offers a number of debugging tools that help identify potential flaws in the code and therefore avoid errors. Some of these tools include the following:

The Break Mode prompts when choosing the Debug option from a runtime error message box. Then, the VBA editor highlights in yellow the line of code that created the error in the particular procedure and module.

The Step Into command is a very helpful tool that allows to run the code line by line each at a time. It can be run by clicking Step Into under the Debug tab in the VBA editor or pressing F8, and it moves the execution into break mode.

 

Toggle Breakpoints can be added to interrupt the code at a certain line in a procedure. These can be inserted by clicking Toggle Breakpoint under the Debug tab in the VBA editor or pressing F9. When reaching the breakpoint, the macro enters break mode.

The Locals Window can be accessed under the View tab in the VBA editor and shows the value and type of all variables or expressions for a given procedure. It can be very helpful in combination with the Step Into command to show changes to values of variables throughout the code. If we use the Locals Window with the example above, we can see the two declared string variables uName and uDept. At the beginning of the procedure the variables are empty (“”).

If we Step Into the procedure with F8, we can see how the value of the variables in the Locals Window changes as the code progresses.

The Watch Window shows the value and type of variables or expressions that have previously been added as a ‘Watch’ to the VBA project. These are added for a specific variable/expression and context (e.g. a particular procedure in a given module). Additionally, a watch type must be selected from the following three options: 

• Watch expression: It just displays the value in the Watch Window
• Break when value is True: Stops code execution and enters break mode when a particular variable or expression is True.
• Break when value changes: Stops code execution and enters break mode when the value of a particular variable or expression changes.

The Immediate Window allows to execute code on the fly just by entering any VBA statements and pressing enter. This can be useful to test some basic statements without having to run or step into the macro. It can be accessed under the View tab of the VBA editor.

Error Handling

Error Handling is a piece of VBA code that anticipates and reacts to runtime errors. It should be included whenever the program interacts with the user or other components of the computer.

The ‘On Error’ statement enables error handling in VBA programming. It must precede the code that is anticipated to generate the error. There are three possible reactions to an error with the ‘On Error’ statement:

On Error GoTo label jumps to a defined labeled line in the code if an error occurs. The labeled location must be indicated with the label followed by a colon (label:). In the example below an On Error statement is added to anticipate user input not compatible with the declared type of variable (Integer) to get the employee ID number. If user does not enter an integer it generates an error. In that case the On Error statement reacts and moves the code to the errorHandler label.

 Sub UserInput()

 Dim empID As Integer

 On Error GoTo errorHandler

 empID = InputBox("Enter Employee ID")

 Exit Sub

 errorHandler:

 MsgBox "Invalid ID"

 End Sub

On Error Resume Next ignores errors and moves to the next line without causing any interruption to the code execution. This is very useful when we anticipate an error but do not want to do anything about it, and just avoid the code breaking.

On Error GoTo 0 will disable any previously set error handling within the current procedure.

The Err object stores information about the runtime errors that can be accessed through the Number and Description properties with: Err.Number or Err.Description

Err.Number equals 0 when no error occurs. This and other codes can be used with IF statements to handle different types of errors. Err.Description can be useful to show more information about the error to users. 

<<< Previous