Debugging VBA Code: Adding Breakpoints

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

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 1b.jpg

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

Example 2b.jpg

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!

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!

Share This Post!

Did you find this post helpful? Do you want to support this blog because you're just that awesome?!  By sharing this post on Facebook, Twitter, or Google+ you are not only providing others with great information, you are creating exposure for The Spreadsheet Guru.  Growing this community is my number one goal as I have found learning to increase exponentially when lots of people are contributing to the conversation.  Learning is the whole reason why this blog exists!  If you want to spread the word just click on the Share button right below this paragraph (next to the Like button -- I enjoy "likes" too!) and select your preferred social medium.  Thank you so much for reading and I hope I can continue to provide you with great content in the future!  Cheers!