15  Debugging

Debugging is the process of identifying and fixing errors in code. It is an essential skill for programmers to develop. In this course, you will encounter various errors while writing VBA code. Understanding how to debug your code effectively will help you become a better programmer.

Figure 15.1: Do you trust your code?

15.1 Common Types of Errors

Errors in VBA code can be broadly classified into three categories:

  • Syntax Errors: These errors occur when the code violates the rules of the VBA language. Syntax errors are usually detected by the VBA editor and are highlighted with a red underline.
  • Runtime Errors: Runtime errors occur while the program is running. They can be caused by a variety of factors, such as invalid input, incorrect logic, or unexpected conditions.
  • Logic Errors: Logic errors are the most challenging to identify. They occur when the code runs without throwing any errors but produces incorrect results. Logic errors require careful analysis of the code to identify and fix.

15.2 Debugging Techniques

Here are some common techniques you can use to debug your VBA code:

  1. Immediate Window: Use the Debug.Print statement to output information to the Immediate Window within the VBA editor.
  2. Breakpoints: Set breakpoints in your code to pause execution at specific lines and inspect the state of variables.
  3. Watch Window: Use the Watch Window to monitor the value of variables as the code executes.
  4. Step Into: Use the Step Into feature to execute the code line by line and observe the changes in variables.
  5. Error Handling: Implement error handling routines to gracefully handle exceptions and provide meaningful error messages.
  6. Message Boxes: Display message boxes with relevant information to help diagnose issues.
  7. Logging: Write output to a log file to track the program’s execution and identify errors.
  8. Code Review: Have a peer review your code to identify potential issues and suggest improvements.
  9. Testing: Write test cases to verify the correctness of your code and catch errors early in the development process.
  10. Rubber Duck Debugging: Explain your code line by line to an inanimate object (like a rubber duck) to identify issues. Many times, just by articulating the problem, the solution becomes apparent. For example, you can use ChatGPT as a “virtual rubber duck” to help you debug your code.
  11. Assertions: Use Debug.Assert statements to validate assumptions in your code and catch unexpected conditions. For example, you can assert that a variable should never be Nothing at a certain point in the code. If it is, the assertion will fail, indicating a problem. Or, you can assert that a value should be within a certain range. For example: vb Debug.Assert x > 0 And x < 100, "x should be between 0 and 100" If the condition is not met, the assertion will fail, and a message will be displayed in the Immediate Window.