Debugging in Excel VBA

Debugging in VBA: Debugging is a technique used to fix errors in programming languages. In Excel VBA, we have different ways by which you can identify the error in the code and resolve it.:

The commonly used methods are as follows:

• Breakpoint
• Stepping

1. Breakpoint

A selected program line at which execution will automatically stop and then you can see if the error has occurred till that line or not – if the error hasn’t occurred, then you can be sure that the remaining part of the code has the error statement. The Breakpoint is a useful technique in long procedures where it would be tedious and time-consuming to run through each and every line of code before you get to the part that you are trying to debug.

Apply Breakpoint

To add a breakpoint, click on the right-side margin of the line you want the execution to stop at or use the F9 as a shortcut key.

Remove Breakpoint

To remove a breakpoint, either click on the red dot or place the cursor on the specified breakpoint line and again press F9. Next, click on the green arrow to continue execution.

Program: In the below program, the execution will stop at 3rd line of the program before ‘Else’.

Sub BreakpointExample()
 If Worksheets("Sheet1").Cells(2, 1).Value = "India" Then
 'enabling the breakpoint and halting the process
 Worksheets("Sheet1").Cells(1, 1).Value = "Delhi"
 Else
 Worksheets("Sheet1").Cells(1, 1).Value = "Delhi"
 End If
 End Sub 

Output

2. Stepping (for Run-time error debugging)

If while running the code, excel throws an error, you can step into the code and go from one line to another by pressing F8 and see which line excel has thrown an error for. It is the easy and commonly used debugging method is to manually check every line and understand which line gave the error, we use ‘Step-Into’ from Debug menu or press F8 –

As we keep on pressing F8, VBA will keep on highlighting the lines –

As we try to move from this line to another, VBA gives the same error which we got when we ran the entire code suggesting that the error occurred because of this 3rd line which is right – as we are passing a text value to a variable which has been declared as a date variable.

This feature is even more useful when the code is complex or has a lot of lines – this example had just 4 lines.