Debugging macros in Excel Visual Basic / VBA
Part three of a five-part series of blogs

This series of blogs is intended to provide online training in how to debug in Visual Basic for Applications, including using breakpoints and the immediate window.

  1. Debugging Macros
  2. Ways to Step Through Code in VBA
  3. Setting Breakpoints and the VBA Stop Statement (this blog)
  4. Debug.Print and the Immediate Window
  5. Other Useful Debugging Tools

This blog is part of our Excel VBA tutorial.  You can also learn to program in Visual Basic for Applications on one of our scheduled or tailored VBA training courses.

Posted by Andy Brown on 25 November 2011

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Setting Breakpoints and the VBA Stop Statement

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:

Breakpoint shown

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.

 

Setting and Removing Breakpoints

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

Clicking in grey margin

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.

A surprisingly useful short-cut key is SHIFT + CTRL + F9, which removes all of the breakpoints that you've set.

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:

Sub CheckAnagrams()

'each letter number in the alphabet

Dim i As Integer

'first get the words from spreadsheet

GetWords

'if they're not the same length, the answer is no

If Len(FirstWord) <> Len(SecondWord) Then

GiveVerdict False

Exit Sub

End If

'break code here

Stop

'check each letter appears the same number of times

For i = 1 To Len(Letters)

If LetterDifferent(Mid(Letters, i, 1)) Then

GiveVerdict False

Exit Sub

End If

Next i

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

Stop statement about to run

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:

Example of debug.assert

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.

 

This is an odd command: not only does it break when the condition is False, rather than True, but it has a very strange name too.

Now that we've started talking about the Debug.Assert statement, it's time to look at the much more powerful Debug.Print statement, and with it the immediate window.

 

This blog has 0 threads Add post