VBA: How to Debug Code

VBA: How to Debug Code

When writing and running VBA code, it is important for the user to learn how to debug code errors. It is the expectation of the Excel user that the code will work as expected. However, there are instances when users encounter errors and bugs in the code that may affect its integrity. The code will often run into problems, and the user will need to debug the errors first before they can continue writing additional lines of code.

While some of the errors may be highlighted and warnings displayed, there are errors and bugs that require users to re-examine their code to find, expunge any bugs they find, and solve errors that affect the smooth execution of the code.

Examining Your Code

When examining the VBA code for errors, the most straightforward debugging technique is to review the code. It requires looking at the code afresh in an attempt to check any existing errors or other unintended features. Finding errors using the review method requires knowledge of VBA code and the experience to detect such errors. It may help identify errors, but it is not as efficient as the built-in debugging tools.

The VBA Editor comes with a built-in debugging mechanism that allows Excel users to interact and run the code. When the user launches the VBA Editor (by pressing Alt+F11) in Excel, the editor window opens, and there is a debug option at the top menu, as shown in the figure below:

When the user clicks the debug menu, a drop-down list will appear, containing a list of commands that can be used to debug the VBA code. The commands come with shortcut keys that users can use to execute the command.

Run Current Procedure (F5)

One of the methods used to debug VBA code is by running the code. The shortcut key for the command is F5. Start by placing the cursor into the UserForm or Sub (macro) and then press F5 to run the sub. Please note that F5 will not work when running a sub that requires parameters to execute a function.

Stepping Over Code

Stepping over code is one of the commands available under the debug menu of the VBA Editor. The command requires users to skillfully step over each line of the VBA code in the second sub. If the code calls for another sub, simply step over the second sub and continue running the code. To run the code, press Shift+F8 simultaneously to execute the second sub.

Stepping Out of Code

The stepping out command helps the user step out of the current running sub. The shortcut for the command is to press Ctrl+Shift+F8. It can be used when the user wants to step out of a sub that they entered either intentionally or accidentally. When the command is executed, the code in the current running sub will be executed. However, the code will stop at the next statement after the call to the sub.

Breakpoints

The breakpoint specifies the line in your code where VBA should pause the execution of the macro operation when debugging the code. Specifying the breakpoint helps prevent VBA from running into a loop of IF statement.

The shortcut key for adding a breakpoint is F9. Create a breakpoint, position the cursor on the specific line of your code where you want VBA to pause, and press F9. Alternatively, locate the line of code where you want to add the breakpoint and click on the left grey margin beside the line.

A dark red dot will appear on the margin beside the specified line to indicate that the breakpoint has been created. The specified line of code will also be highlighted with the same color (dark red). The VBA Editor allows the user to set as many breakpoints as is required when debugging the code to check that it is working correctly.

To remove the breakpoint, simply click on the dot again or press F9 with the cursor positioned in the highlighted line of code. If there are many breakpoints, the user can remove all of them at the same time by pressing Ctrl+Shift+F9.

Using VBA Debug.Print

The Debug.Print tool is a useful feature in the VBA editor that helps Excel users to evaluate how the code is working. In addition, it analyzes changes in the variables created in the program. Debug.Print is an alternative to the MsgBox feature in VBA, which is also used to display the output of the window when running the program.

Unlike Msgbox, Debug.Print eliminates the need for any confirmation or acknowledgment every time. It is also used to show the values of variables by displaying a log of returned values in the immediate window. The tool is effective in evaluating a code to confirm that it is working properly, as well as detecting any bugs in the code. It prints out the variables, numbers, arrays, and strings in the active and empty Excel sheets.

Additional Resources

CFI offers the Business Intelligence & Data Analyst (BIDA)® certification program for those looking to take their careers to the next level. To keep learning and advancing your career, the following resources will be helpful:

• Excel VBA Examples
• How to Add a VBA Button in Excel?
• VBA Cell References
• Transitioning from Excel to Python
• See all Excel resources