20.2 Debugging

Let’s begin this lesson by taking a look at how we can debug our VBA programs. Despite our best efforts, sometimes errors are introduced into our code. We want to look through the code and make sure there are no errors or bugs in it. So, as we’ve seen earlier in this course, the process of debugging is simply to remove bugs or issues in our code that prevent it from executing correctly.

To introduce you to these concepts, we’ll use the following code.

Function REVERSETEXT(textVar) As String
     'Returns its arguments, reversed
     Dim TextLen As Long, i As Long
     TextLen = Len(textVar)
     For i = TextLen To 1 Step -1
          REVERSETEXT = Mid(textVar, i, 1) & REVERSETEXT  'with error
     Next i
End Function

Paste this code into a VBA module. This code has some errors in it, and we will use a few different approaches to help us find the errors or bugs. Once you’ve pasted it into a module, switch back to the worksheet, save it, and try to use the function.

The purpose of this function is to take a text’s value and reverse it. So, the word EXCEL should return LECXE.

=REVERSETEXT("EXCEL")

However, the code above simply returns EXCEL. So, we’ve got an error in our code.

We could sit and stare at the code and try to figure out where we went wrong with the logic or whatever the code is running into. However, it is a lot easier to stop the code mid-execution and investigate some of the parameters. To do that, we can add a debug point in our code by left-clicking on the margin. It will add a red dot, indicating a debug point.

These debug points can’t be added to lines of sub procedure or function declarations or to where variables are being declared. Once a debug point is added, when the function is called, it will stop at the line of code where the debug point is added before that line of code is executed. You can then execute one line at a time by pressing the F8 key.

When you are in debug mode, you can just hover over each of the variables in the code and see the values currently assigned to them. For example, textVar has the value EXCEL, which looks correct. After pressing the F8 key once, the TextLen variable’s value is 5, which is correct.

Notice that you can also use the Immediate window when you are debugging to see the values of variables or to evaluate expressions. The Locals window shows the values of all variables currently in scope. You can also right-click and add watches for specific variables or expressions and monitor those as you step through your program.

Continue to execute your program, one line at a time. We’re going to go from the end back to the beginning, taking it one step at a time in reverse order—from 5 to 4 to 3 to 2 to 1. Let’s go ahead and hit F8 to step over each piece of code and see if we can figure out what’s going on here.

Initially, the reverse text is nothing. We’ll go ahead and do the Mid, then add REVERSETEXT to it. After the first loop, it is L, just as expected. That looks like it’s doing something right. As we continue into the second loop, it becomes EL. What’s going on here?

REVERSETEXT = Mid(textVar, i, 1) & REVERSETEXT  'with error

It looks like the REVERSETEXT should instead be on the left side of the Mid so each character is added to the end.

Okay, before fixing that, I wanted to show another way we could address this rather than stepping through one line at a time. Optionally, we could have added Debug.Print statements inside our loop.

For i = TextLen To 1 Step -1
     REVERSETEXT = Mid(textVar, i, 1) & REVERSETEXT  'with error
     Debug.Print i & "-" & REVERSETEXT
Next i

Remove the debug point by left-clicking on it. Now, re-run the function by editing it and hitting Enter. The Debug.Print command will output the results to the Immediate window. Notice the output.

5-L
4-EL
3-CEL
2-XCEL
1-EXCEL

With that additional bit of debug code, we can easily see that during each iteration of the loop, we are adding the letter before the result, and that will likely draw our attention to the place in the code where we need to make adjustments. Alternatively, we could have simply looked at the local variables to see how the values changed as we stepped through the code.

So, what changes need to be made? I’ll simply make sure I add each new character after the REVERSETEXT for every iteration of the loop.

REVERSETEXT = REVERSETEXT & Mid(textVar, i, 1)  'fixed