This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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. 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.

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.

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

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 two-day Introduction to VBA course - if you book the online version you can attend this 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.

Parts of this blog
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

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC