Debugging Macros

It is a sad fact that nearly every macro that you ever write will contain bugs initially.  This tutorial explains how to debug VBA macros (that is, how to find any mistakes in them).

Splitting the Screen

The best way to debug a macro is to split your screen.  The easiest way to do this in Windows 11 is as follows:

Hover over the icon to restore, minimise or maximise your Excel or VBA window and you should be able to choose to split your screen vertically like this.

Basic Debugging

If you want to debug, you can now press the F8 key to step through a macro line by line:

You can also use the tools on this Debug toolbar, but I find the short-cut keys easier. To display this toolbar, just right-click on any existing VBA toolbar and choose Debug.

Excel will show the line that you are about to execute in yellow:

Here we are about to run the first line of this macro, so it appears in yellow.

When you press the F8 key to execute the line above, VBA will omit the variable declaration and go straight to the line after it:

Variable declarations are what is called non-executable (they just set aside space in memory).

You can now continue pressing the F8 key until your macro finishes.

Stopping a Macro Prematurely

If you've decided that you want to stop a macro running, you can do so by clicking on the Reset tool:

Click on the blue square to stop your macro running.

If your macro crashes (perhaps because it's looping infinitely), you can press Esc (this is easier to find on your keyboard, and usually works), or failing that try to find the Break key.

Showing the Value of Variables

At any time you can let your mouse linger over a variable or property to show its value:

Here the value of the variable i is shown as 0 (hardly surprising - we haven't set it to anything yet).

There are lots of other ways to display the current values of variables, but this is the easiest.

 Our Example

Throughout the rest of this debugging tutorial we use an example to work out whether two words or phrases are perfect anagrams of each other:

The macro checks whether the two words entered are (give or take spaces) perfect anagrams of each other.

Here is our code (I don't claim that this is a particularly efficient algorithm, by the way!):

In a nutshell:

1. The CheckAnagrams routine first calls the GetWords procedure, which reads the words entered into the two variables FirstWord and SecondWord, strips out any spaces and converts all letters to lower case.

2. The CheckAnagrams routine then compares the lengths of the two words entered, and reports an error if they're not the same.

3. Finally, the CheckAnagrams routine calls the LetterDifferent routine 26 times (once for each letter of the alphabet).  For each time, the LetterDifferent routine removes the given letter from each of the two words, and determines if the new words that this would yield are still of the same length.

Now that we've seen what our example is, let's look at how to debug it, starting with the basic debugging keys.

Keys to Step through Code

As mentioned, you can use the F8 key to step through code, but what happens when you reach a call to another routine?

In the example shown, the CheckAnagrams macro is about to call the GetWords macro.  What will happen next depends on which function key you press.

Here are the two main keys that you can press, and what each would do:

Changing the Next Executable Line

The above keys are great for stepping forward through a macro line by line, but whoever said you had to do this?  What happens if you want to go backwards, or jump forwards?  The easiest way to do this is to click and drag on the yellow arrow:

Want to run the GetWords routine again? Just drag the yellow arrow to the line which you want to execute next, and Excel will make this the next executable statement.

Another way to achieve the same effect (though not nearly as much fun) is to right-click on the line which you want to execute next:

Right-click on any executable line to make this the next statement to be executed (it will go yellow).

Setting and Removing Breakpoints

Often in a complicated or long macro you will want to avoid stepping through it line by line.  To do this you can set a breakpoint:

Here we've set a breakpoint halfway down the CheckAnagrams macro. If you run this macro, it will execute until it reaches the breakpoint, and then allow you to press the F8 key to step through the code line by line.

You can set or remove a breakpoint by clicking in the margin to the left of it:

Click in the grey margin to the left of a line of code to set (or remove) a breakpoint for it.

Alternatively, press F9 to toggle a breakpoint on or off.

Permanent Breakpoints

One feature of breakpoints is that they are not saved wihen you close a workbook - so if you come back to your program on the next day, you'll have lost any breakpoints that you set.  A way round this is to use the STOP statement instead, as in this example:

When you run the code above, this is what will happen:

VBA will run the code until it reaches the STOP statement, then allow you to step through your code line by line.

When you save this workbook, the Stop statement will be saved with it.

Conditional Breakpoints

Although I don't use it much, for the sake of completeness I should mention the Debug.Assert statement, which will take you into debug mode if a condition is true:

Here when we run the main macro it will stop when the condition following the Debug.Assert statement is False.  To say the least, this is counter-intuitive.  The first break in this code will thus come when i is first not less than 20, as shown here.

Debug.Print and the Immediate Window

The immediate window is an invaluable tool for debugging. You can display it like this:

Choose this option or press Ctrl + G.

You can ask any question in the immediate window by preceding it with a question mark:

Here we've asked for today's date, the current time, the name of the current workbook and the name of the active sheet.

However, you can also use the Debug.Print statement to write things to the immediate window - for example:

This subroutine would show the following:

Notice that we have the output from the previous commands showing at the top.

The only way Wise Owl have ever found to clear the immediate window is to select its entire contents (press Ctrl + A) then press Del to delete the text selected.

 You can print multiple things to each line of the immediate window by separating them with commas. For example, this modified subroutine:

would produce this output:

You'll find columns don't always line up neatly like this.

The Call Stack

When you are in a procedure called from another, it can often be useful to see whereabouts you are in the call stack.  This is easier to understand with a diagram:

The green arrow shows where you are in the calling routine called CheckAnagrams.

To display the call stack, while debugging a macro and in break mode either press CTRL + L or choose the menu option shown below:

Choose this option to display the call stack.

You can then double-click on any procedure listed to go to the current cursor position within it:

Double-click on any routine to go to the currently executing line within it.

The Locals Window

The Locals window isn't particularly well named, as it allows you to see the value of local and global variables.  It might be better called the Variables window.  To display it, while in break mode (ie while debugging):

Choose the menu option shown to display the Locals window.

Here's an example of the Locals window:

In this window you can see the value of local and (if you expand Module1) global variables.

Watching Variables

The Locals window indiscriminately displays the value of all currently used variables, but sometimes you may want to watch the value of a variable to see - for example - when it changes.  You can do this as follows:

Double-click on any variable to select it, then right-click and choose to watch it as shown here.

The next stage is to choose how to watch it - perhaps the most useful way is shown here:

Here we are choosing to go into break mode whenever the value of the variable called Word1Reduced changes.

When watching variables like this, you will find it useful to have the Watch window open!

Choose the menu item shown to display the Watch window.

In the example below, we're watching two variables, but one of them is shown as Out of context:

Here we're watching two variables.

Here the variable FirstWord is in a different subroutine to the current one, and so at the moment its value is not set.

Now go zap those bugs!