Debugging VBA Code

• Share on Facebook.
• Share on Twitter.
• Share on Google+
• Share on LinkedIn
• Pin It!

When you write VBA, or any programming language, you are going to encounter errors in it, or should we call them unintended features? Basically you can't write any substantial amount of code without needing to fix errors and make sure that it works as it's supposed to.This post is going to look at the tools I use the most in the VBA editor to help debugging VBA code.

These debugging tools not only let you fix problems, they allow you to gain a better understanding of code, so can be used to familiarise yourself with code written by someone else.

Getting Started

The first thing you need to do is open the VBA editor, press ALT + F11 in Excel.

Of course you need a bit of code to debug, so here's a trivial example. This code is available to download in a workbook.

Sub ListNumbers()

    Dim counter As Integer
    Dim myRange As Range
    
    Set myRange = Range("B11")
    
    For counter = 0 To 10
    
        myRange.Offset(counter).Value = counter - 1
        
    Next counter
    
    ChangeFont

End Sub

Sub ChangeFont()

    ActiveCell.Font.Bold = True
    
End Sub

The Debugging Tools

When I say 'tools' what I am referring to are ways the VBA editor allows us to interact and run the code.

If you look at the Debug menu in the VBA editor this is what you will see

Almost all the tools (or commands) have a shortcut key and you should learn these as it's a much quicker way to debug, rather than going to the menu all the time.

Running Code : F5

Whilst this isn't listed in the Debug menu, you will use it when debugging.

When you place the cursor into a sub, either with a mouse or using the keyboard, press F5 to run that sub.

Also, if you are stepping through code (F8 - see below) and get to a point where you are happy with what you have checked, you can press F5 to execute the rest of the code.

You can't use F5 or F8 to run a sub that requires parameters, or to execute a function.

Stepping Through Code : F8

With the cursor in the sub, press F8 to execute one line of code at a time.

The next line to be executed will be highlighted in yellow, with a yellow arrow pointing to it.

Each time you press F8, the line highlighted in yellow is executed, and the next line to be run is highlighted.

Notice that the DIM statements declaring variables are not included in this and are stepped over.

You can press F8 to execute every line of code, one at a time, until the sub ends.

Stepping Over Code : SHIFT + F8

If your code calls another sub, you may not want to step through each line of code in that 2nd sub. In this case you can 'step over' the 2nd sub and immediately continue executing the code in your 1st sub.

My sub ListNumbers calls another sub ChangeFont. When code execution gets to the call for ChangeFont, I can press SHIFT + F8, the ChangeFont sub is executed, but I don't have to wait for this, and the code pauses again at the next line which is the End Sub statement.

Stepping Out of Code : CTRL + SHIFT + F8

If I find myself in a called sub, either intentionally or accidentally because I pressed F8 too many times, I can Step Out of that sub by pressing CTRL + SHIFT + F8.

What this means is that the code in the sub will be executed, but code execution will pause at the next statement after the call to the sub.

So in my code, if I am in ChangeFont, stepping out of this sub will pause the code at the End Sub statement of ListNumbers.

Breakpoints : F9

A breakpoint is a line in your code where you tell VBA to pause and wait.

To create a breakpoint you can either position the cursor on the desired line and press F9, or just click in the margin beside that line. A dark red/brown dot will appear beside the line in question to indicate a breakpoint has been set, and that line of code is highlighted in the same color.

Clicking on the dot again will remove the breakpoint, as will pressing F9

You can set as many breakpoints as you want so removing them all can take some time if you have a lot of code. To make this easier, pressing CTRL + SHIFT + F9 removes all breakpoints.

You can use breakpoints to interrupt the code in a function and check that it is working.

Run to Cursor : CTRL+ F8

This works in a similar way to breakpoints, but are not not set. With a breakpoint, that breakpoint exists until you remove it. With run to cursor, it only works once.

To do this, position your cursor on the line where you want code execution to pause and press CTRL + F8

Changing the Next Line to Execute

As you are stepping through code, you'll notice that the next line to execute is highlighted in yellow, and has a yellow arrow pointing to it in the margin.

You can use your mouse to drag this arrow to whatever line you want in the same sub (provided it's an executable line), and execution will then continue from there.

This is extremely useful if you want to alter the value of something or fix a bug and then re-execute the code to see if your changes have the desired effect.

So if I have started my For loop where the variable counter takes the values from 0 to 10, and then decide I want counter to actually go up to 20, I just drag the yellow arrow back up to the start of the For loop, change 0 to 20, then start stepping through the loop again.

That's great I hear you say, but I'm not sure what value counter has. So how do we check that?

Checking the Value of Variables

There are a number of ways to do this, the easiest is to just hover your mouse over the variable while your are debugging:

Debug.Print

Or you can use the Debug.Print statement, I use this a lot. This will print the vale of a variable to the Immediate Window. CTRL + G to show the Immediate Window.

Insert the Debug.Print statement directly into your code like so:

This will only work with 'simple' data types (integer, string, long etc) but won't work with things like ranges.

You can also type the statement directly into the Immediate window (and hit enter) and get the variable's value

Debug.Print will print any string so you can make it more descriptive if you like e.g.

Debug.Print "At this precise moment in time, counter has the value " & counter & ", thanks for asking."

Watches

You can Watch a variable and its value is shown in the Watch window. If your Watch window isn't showing, turn it on from the View menu in the VBA editor.

To watch a variable, place the cursor in the variable, right click and then click on Add Watch.

You now have 3 choices. Don't worry about the Context as you shouldn't need to change this. What you need to decide is do you want to simply watch the variable. In which case you'll see its value change as the code executes. Or do you want to break (like breakpoints) when the variables value is True (for Boolean variables) or break when the value changes.

If we are just interested in seeing the value as it changes then choose Watch Expression, otherwise choose the appropriate Break option.

You'll now be able to see the value of your variable change in real time.

Locals Window

The Locals window is like the Watch window, you can see variable values in real time. Use the View menu to turn it on.

Altering Code While Debugging

As I've demonstrated, you can alter your code on the fly while you are debugging. This is a great way to iron out issues. Just change the offending code, then use your mouse to drag the yellow arrow to change the next line to be executed.

Interacting With the Workbook While Debugging

Similar to changing code whilst debugging, you can interact with your workbook(s) while debugging.

With the code paused, you can change the data in the worksheet, select different sheets or workbooks etc.

Be aware of doing this as it may cause unintended results for any code that refers to the ActiveCell or ActiveWorkbook as my ChangeFont sub does.

Please Share

If you liked this please click the buttons below to share.