BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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.
- Debugging Macros
- Ways to Step Through Code in VBA
- Setting Breakpoints and the VBA Stop Statement (this blog)
- Debug.Print and the Immediate Window
- 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:
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.
WE cover breakpoints and debugging - and much else besides - on our live two-day Introduction to VBA online course, which you can attend from anywhere in the world!
Setting and Removing Breakpoints
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.
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:
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.
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.
- Debugging Macros
- Ways to Step Through Code in VBA
- Setting Breakpoints and the VBA Stop Statement (this blog)
- Debug.Print and the Immediate Window
- Other Useful Debugging Tools