16 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.

16.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.

16.2 Debugging Techniques

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

Immediate Window: Use the Debug.Print statement to output information to the Immediate Window within the VBA editor.
Breakpoints: Set breakpoints in your code to pause execution at specific lines and inspect the state of variables.
Watch Window: Use the Watch Window to monitor the value of variables as the code executes.
Step Into: Use the Step Into feature to execute the code line by line and observe the changes in variables.
Error Handling: Implement error handling routines to gracefully handle exceptions and provide meaningful error messages.
Message Boxes: Display message boxes with relevant information to help diagnose issues.
Logging: Write output to a log file to track the program’s execution and identify errors.
Code Review: Have a peer review your code to identify potential issues and suggest improvements.
Testing: Write test cases to verify the correctness of your code and catch errors early in the development process.
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.
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:
```
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.

16.3 On Error Statement

The On Error statement is used to enable error handling within VBA code. There are several ways to use the On Error statement:

On Error Resume Next: This statement tells VBA to continue executing the code even if an error occurs. It is often used to ignore errors and continue with the next line of code. To enable error handling again, use On Error GoTo 0.
On Error GoTo Label: This statement redirects the execution to a specific label when an error occurs. You can define the label at the end of the subroutine to handle errors gracefully.
On Error GoTo 0: This statement disables error handling and reverts to the default behavior of stopping execution when an error occurs.

In Listing 16.1, we have a function ArrToString that converts an array of integers to a string. The function handles the case when the array is empty by using an error handler to return an empty string.

Listing 16.1: Example of using the ArrToString function to convert an array of integers to a string. The function handles the case when the array is empty by returning an empty string. Notice that Exit Function is used to exit the function when no error occurs, and the EmptyArray label is used to handle the case when the array is empty. Without Exit Function, the code would continue executing after the error handler label.

Function ArrToString(arr() As Integer)

    On Error GoTo EmptyArray
    
    Dim i As Integer, str As String

    str = arr(LBound(arr))
    For i = LBound(arr) + 1 To UBound(arr)
        str = str & ", " & arr(i)
    Next i
    ArrToString = str
    Exit Function

EmptyArray:
    ArrToString = ""

End Function

Sub TestArrToString()

    ReDim arr(1 To 5) As Integer
    Dim i As Integer

    For i = 1 To 5
        arr(i) = i * 10
    Next i

    Debug.Assert ArrToString(arr) = "10, 20, 30, 40, 50"
    
    ReDim Preserve arr(1 To 1) As Integer
    Debug.Assert ArrToString(arr) = "10"
    
    Erase arr
    Debug.Assert ArrToString(arr) = ""

End Sub