Debugging VBA Code: Adding Breakpoints

By Chris Newman •  Updated: 04/09/14 •  4 min read
Debugging Excel VBA Macro Code by Adding Breakpoints

Sometimes it pays to stay in bed on Monday, rather than spending the rest of the week debugging Monday’s code.

Christopher Thompson

The process of debugging IS the most stressful and frustrating stage in writing any type of computer-based code, VBA included!  This process intensifies tenfold if you don't know the techniques or how to use the built-in tools to assist you in troubleshooting your code.  In the following sections we will cover a very basic debugging technique that will save you a ton of time as you work through fixing errors in your code.  The debugging technique is Inserting A Breakpoint.

What Are Breakpoints?

A Breakpoint pauses the execution of your could on a designated line.   This is helpful when debugging your VBA code because it allows you to run parts of your code that are bug-free while pausing the execution of your macro in areas that need further testing.  

As an example let's say you have 50 lines of code and the first 20 lines of the macro run perfectly.  However, around line 21 you are receiving an unexpected result.  Instead of stepping through each line of you code (by keying F8 twenty times!), you can simply add a breakpoint on line 21 of you code.  You then can play your code (key F5) and your code will execute instantly through til it reaches line 21.  Once the Visual Basic Editor is paused, you can then investigate what is causing your code to output the unexpected results.  Proper use of breakpoints will save you HUGE amounts of time while you are debugging!

How To Insert A Breakpoint

Another great thing about breakpoints is they are extremely easy to add and remove from your code.  Here are a couple ways you can add a breakpoint to a line of VBA code:

  1. Placing your cursor on a code line and keying F9
  2. Clicking in the left margin next to the desired code line
How To Insert Breakpoints

Note you CANNOT add breakpoints to lines containing

  • Nothing in them (blank lines)
  • Only code comments (lines with an apostrophe as the first character)
  • Dimensioning statements (ie Dim x as Long)

Removing Breakpoints

To remove a single breakpoint you can either

  • Rekey F9 when the cursor is within the breakpoint code line
  • Re-click the breakpoint icon in the left margin

To remove all breakpoints use the shortcut  key Ctrl + Shift + F9

Example Situations For Using Breakpoints

Only pause code when a certain criteria is true in an IF statement.  In the example shown below, the macro will only break when a cells value is equal to 25.

Example Situations For Using Breakpoints 1b

The below image shows how you can pause your code before you run another VBA macro.

Example Situations For Using Breakpoints 2b

Below shows how you can pause the code right after it has completed going through a large loop.  It would waste so much time if you had to individually step through each line  in the loop just to get to the next section of your code.  This setup allows you to run through the loop in a split second!

Example Situations For Using Breakpoints 3

How Do You Debug?

Using Breakpoints will save you valuable time while attempting to fix errors in your code.  I want to hear from you about other techniques everyone can use to help debug VBA code faster.  Leave a comment below explaining your methods.  I look forward to hearing your thoughts!

Keep Learning

Chris Newman

Chris Newman

Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.